Postgresql: how to calculate the percentage correctly?

With this very simple query I obtain the count(total) and genre of movies from my db.

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;

Answer

Use window functions:

select genre,
       count(*) as cnt,
       count(*) * 1.0 / sum(count(*)) over ()
from titles
group by genre
order by cnt desc

Advertisement

Answer