Find Percentage of Rows that Satisfy Certain Criteria

Question

I have a table with multiple columns, and I need to find the percentage distribution of the different value for one column, but only considering rows that satisfy certain criteria in a different column.

For example if I have:

name  |  hat_color | hometown
--------------------------------
John  |  Blue      | NYC
Mary  |  Blue      | NYC
Joe   |  Red       | NYC
Steve |  Yellow    | NYC
Bill  |  Blue      | Chicago
Sue   |  Yellow    | Chicago

I want a query that tells me the percentage of each hat color in NYC:

hat_color |  Percentage
Blue      |    50
Red       |    25
Yellow    |    25

I've tried:

select hat_color, count(hat_color) * 100.0 / (select count(*) from mytable) as Percentage
from mytable
WHERE mytable.hometown="NYC"
group by hat_color;

But that doesn't work, and just gives me the overall percentages as if it was counting the whole table and not just NYC in the divisor.

I'm assuming my "where" is misplaced? Where should it go?

Answer 1

You also need to filter for NYC in your sub select.

SELECT hat_color,
       count(hat_color) * 100.0 / (SELECT count(*)
                                          FROM mytable
                                          WHERE hometown = 'NYC') percentage
       FROM mytable
       WHERE hometown = 'NYC'
       GROUP BY hat_color;

And don't enclose string literals in double quotes. In SQL single quotes are used for that.