jonlink jonlink - 10 months ago 29
SQL Question

Percentage of an Occurrence in a Column

Looked, but only found down voted questions with no good answers.

I'm trying to find the percentage of an occurrence in MS SQL. A simplified version of what I'm doing:

1 Jim Blue
2 Tom Green
3 Rob Yellow
4 Kev Blue
5 Kim Green
6 Bev Blue

I can count how many of each, that's easy.

SELECT favorite_color, COUNT(favorite_color) AS totals
FROM color_prefs
GROUP BY favorite_color

That'll tell me 3 people like Blue (etc). What I can't figure out how to do is get the percent. I want to know that 50% of people like Blue. I tried the below, but SQL totally hated it:

SELECT favorite_color, COUNT(favorite_color)/SUM(COUNT(favorite_color)) AS color_percent
FROM color_prefs
GROUP BY favorite_color

I get the error:

Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

Obviously, I could make PHP figure this out for me, but I there's got to be a way to do this in SQL without subqueries, right?


You can do it in two ways..first way is not so efficient, but if your table does not contain a lot of rows, then it should be ok..

select favorite_color,count(favorite_color)*100/(select count(*) from a)
from color_prefs
group by favorite_color

This is the second way and is more efficient..

select favorite_color,count(favorite_color)*100/sum(count(*)) over()
from color_prefs
group by favorite_color

check the sqlfiddle