CODEWITHSUNDEEP
sqlpostgresql
elbarna
elbarna

Reputation: 737

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;

Upvotes: 1

Views: 2149

Answers (3)

sia
sia

Reputation: 577

The other solution would be using scalar subquery

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

Upvotes: 1

elbarna
elbarna

Reputation: 737

Thanks to Gordon for the correct answer, using this string I obtain a perfect result(except for 0% results which are the 0.0001... rounded by round function, but is not a problem)

select genre, count(*) as total, round(count(*) * 100 / sum(count(*)) over ()) || ' %' as percent from titles group by genre order by total desc;

     genre       | total  | percent 
-----------------+--------+-------------
 Drama           |    529 | 17 %
 Comic           |    393 | 13 %
 Martial arts    |    276 | 9 %
 History         |    269 | 9 %
 Action          |    237 | 8 %

...

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270463

Use window functions:

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

Upvotes: 3

Related Questions