Select count of multiple columns WHERE another column is distinct

Question

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.

Tag only the database that you use. – forpas Oct 22 '21 at 19:04 — forpas, Oct 22 '21 at 19:04
what are the data types of these columns? – Nadeem Taj Oct 22 '21 at 19:19 — Nadeem Taj, Oct 22 '21 at 19:19

score 1 · Accepted Answer · answered Oct 22 '21 at 19:08

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;

Isolated · Answer 2 · 2021-10-22T19:23:51.777

0

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

edited Oct 22 '21 at 19:23

answered Oct 22 '21 at 19:06

Isolated

5,169
1
6
18

1

Thank you so much! – morganherg Oct 22 '21 at 19:22

Select count of multiple columns WHERE another column is distinct

2 Answers2