With this very simple query I obtain the count(total) and genre of movies from my db.
x
select genre,count(*) from titles group by genre order by count desc;
genre | count
-----------------+-------
Drama | 529
Comic | 393
Martial arts | 276
History | 269
Action | 237
My question is: how to get a percentage? I want something like this
select ??????;
genre | percentage
-----------------+-------
Drama | 30%
Comic | 20%
Martial arts | 20%
History | 15%
Action | 11%
Other | 4%
I have tried a lot of codes taken from gogle and stackexchange before asking, as you can see from this psql history but all fail with error or very strange results.
SELECT
title,
ROUND( AVG( genre ), 2 ) percentage
FROM
titles
INNER JOIN genre
USING(id_genre)
GROUP BY
title
ORDER BY
genre DESC;
select title, round ( AVG( genre ), 2 ) percentage from titles;
SELECT round((count(genre) *100)::numeric / NULLIF(count(*), 0), 2) AS percentage;
Advertisement
Answer
Use window functions:
select genre,
count(*) as cnt,
count(*) * 1.0 / sum(count(*)) over ()
from titles
group by genre
order by cnt desc