Counting multiple fields in SQL

Question

I have a table listing People's First Names, their Age, and their favorite color.

I need to create a query in SQL (In access) that counts how many people like Red, and how many people like Yellow.

So far I have written this:

SELECT COUNT (*) as "People who like Yellow or Red"  
FROM Table1  
WHERE Table1.[Favorite Color]='Red'  
OR Table1.[Favorite Color]='Yellow';  

But this returns the TOTAL count of people who like Yellow/Red (In my case, 5)

I have three people who like Yellow, and two who like Red, how can I show those counts independently of each other?

Answer 1

The generic solution is using the GROUP BY operator. This will partition your data based on who likes what color and then count up each group separately.

SELECT
  Table1.[Favorite Color],
  COUNT(*) as "People who like this color"
FROM Table1
GROUP BY Table1.[Favorite Color];

If you only want Red and Yellow in the output, you can add that predicate:

SELECT
  Table1.[Favorite Color],
  COUNT(*) as "People who like this color"
FROM Table1
WHERE Table1.[Favorite Color] IN ('Red','Yellow')
GROUP BY Table1.[Favorite Color];

Answer 2

You can use a CASE statement to do this:

SELECT SUM(CASE WHEN [Favorite Color]='Red' THEN 1 ELSE 0 END) as 'People who like Red'
       ,SUM(CASE WHEN [Favorite Color]='Yellow' THEN 1 ELSE 0 END) as 'People who like Yellow'
FROM Table1