I need to find out how many people ordered what type of food. My table looks something like this:

As you can see the PersonID can occur more than once.

I need the total count of Hamburgers, Fries, and Soda per unique person.

So ideally my result set would look like this:

From what I've tried, so far this query gets me my desired result for a single value:

SELECT COUNT(DISTINCT Id) AS HId
FROM Table
WHERE Hamburger IS NOT NULL

Which seems a little inefficient if I want to return multiple values in the same table. I tried subquerying this with Hamburgers and Fries but it gets me infinity of the same result. It errored when I tried Union. I can't "WHERE" a distinct value for Id if I was to do a select count on burger, fries and soda.

I've searched up and down StackOverflow and I either find queries on returning distinct multiple columns or distinct by a row.

Thanks in advance.

CodePudding user response：

Use conditional aggregation:

SELECT COUNT(DISTINCT CASE WHEN Hamburger IS NOT NULL THEN Id END) AS HamburgerCount,
       COUNT(DISTINCT CASE WHEN Fries IS NOT NULL THEN Id END) AS FriesCount,
       COUNT(DISTINCT CASE WHEN Soda IS NOT NULL THEN Id END) AS SodaCount
FROM tablename;

CodePudding user response：

If you don't mind vertical results, you could use UNION:

    select 'hamburgers' as food, count(distinct personid) as people
    from table
    where hamburger is not null
    union
    select 'fries' as food, count(distinct personid) as people
    from table
    where fries is not null
    union 
    select 'soda' as food, count(distinct personid) as people
    from table
    where soda is not null