Anthony Anthony - 1 month ago 10
SQL Question

How to get data by count and category

If I have a table like below

Person | Category | Fav Colors
----- | ----------- | ------
one | Native | RED
one | Native | GREEN
two | Non-Native | RED
two | Non-Native | BLUE
three | Native | RED
three | Native | GREEN


How can I get data by the number of people in each category for each color?

i.e.

Fav Color | Native | Non-Native
--------- | --------| --------
RED | 2 | 1
GREEN | 2 | 0
BLUE | 0 | 1

Answer

You could do it like this:

select   `Fav Colors` as `Fav Color`,
         count(case category when 'Native' then 1 end) Native,
         count(case category when 'Non-Native' then 1 end) `Non-Native`
from     tbl
group by `Fav Colors`

SQL Fiddle

NB: you did not mention any requirement for sort order. In case you need one, you could just list the column number(s) in the order by clause:

order by 2 desc
Comments