Search code examples
sqlcountconditional-aggregation

Select count of multiple columns WHERE another column is distinct

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

PersonId (int) Hamburger (varchar100) Fries (varchar100) Soda (varchar100)
1 "Unique burger" "Unique Fry" "Unique Soda"
2 "Unique burger" "Unique Fry" "Unique Soda"
1 "Unique burger" NULL "Unique Soda"
3 "Unique burger" "Unique Fry" NULL

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:

HamburgerCount FriesCount SodaCount
12334243 567456745 2463434

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.

Solution

  • 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;