CODEWITHSUNDEEP
sqlaggregation
helloB
helloB

Reputation: 3582

Generated a percentage based (not counted based) frequency table in SQL

I would like to make a frequency table that is percent based with a single SQL statement. So if I have a table of men and women like this:

Name    Gender   
Allen    M
Jerry    M
Marsha   F
Alice    F

I'd like to end with this table:

Gender   Freq
M        .50
F        .50

I know I can count and group, but how can I divide the counts by group by the total count of the table?

Upvotes: 0

Views: 35

Answers (2)

Unnikrishnan R
Unnikrishnan R

Reputation: 5031

Try with the below query 

SELECT gender,cast(count(1) OVER(Partition by GENDER order by GENDER)/(count(1) OVER( order by (select 1))*1.0) as numeric(4,2)) freq
FROM #temp
GROUP BY gender
ORDER BY Gender desc

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269603

Most SQL dialects support the ANSI standard window functions. Using them you can do:

select gender, count(*), count(*) / sum(count(*) * 1.0) over ()
from t
group by gender;

The * 1.0 is simply because some databases do integer division of integer.

Upvotes: 1

Related Questions