Find relative percentage

Question

I have a poll with votes from users. For example:

Do you want a Fiat?

id | answer
1    yes
2    no 
3    yes
...
25   no

count = 20 yes / 5 no

(20 * 100) /25 = 80% Yes
(5 * 100) /25 = 20% No

So, 80% want a Fiat, and 20% don't want. Obviously I can do something like:

select answer, count(*) as total from fast_survey group by answer;

However this will show the count and I am looking for the relative percentage. Any idea how can I do that?

As always, actual table definition (`CREATE TABLE` statement) and Postgres version would be instrumental. — Erwin Brandstetter, Dec 23 '19 at 22:58

Erwin Brandstetter · Accepted Answer · 2019-12-23T23:02:30.537

SELECT round(count(*) FILTER (WHERE answer)     * 100.0 / count(*), 2) AS pct_yes
     , round(count(*) FILTER (WHERE NOT answer) * 100.0 / count(*), 2) AS pct_no
FROM   fast_survey;

pct_yes | pct_no
--------+-------
  80.00 |  20.00

db<>fiddle here

I multiply with 100.0 (not ~~100~~) to avoid integer division. The result is type numeric which can be fed to round() to prettify. See:

Calculating rates in PostgreSQL

Assuming answer is boolean. Else, adapt.

The aggregate FILTER clause has been introduced with Postgres 9.4. See:

How can I simplify this game statistics query?

Should be as fast as it gets.

Nick · Answer 2 · 2019-12-23T22:55:28.983

You can COUNT over partitions to get the values for each answer type:

SELECT DISTINCT answer,
       COUNT(*) OVER (partition BY answer) AS total,
       COUNT(*) OVER (partition BY answer) * 100 /
       COUNT(*) OVER () AS percentage
FROM fast_survey

Demo on SQLFiddle

If you want more precision in the percentage (for the above query it's an integer divide), cast the first COUNT to a FLOAT:

SELECT DISTINCT answer,
       COUNT(*) OVER (partition BY answer) AS total,
       CAST(COUNT(*) OVER (partition BY answer) AS FLOAT) * 100 /
       COUNT(*) OVER () AS percentage
FROM fast_survey

Demo on SQLFiddle

score 0 · Answer 3 · answered Dec 23 '19 at 22:43

How about something like:

SELECT 
    t1.answer, t1.votes / t2.total_votes
FROM
    (SELECT answer, count(*) AS votes FROM fast_survey GROUP BY answer) AS t1,
    (SELECT count(*) AS total_votes FROM fast_survey) AS t2
;

This will also work if your survey has multiple answers.

score 0 · Answer 4 · answered Dec 23 '19 at 22:43

just join it with the table where you ciunt all records without division on answers and count percentage...Something like this

select answer, count(*) / max(totalCount) * 100 as total from fast_survey group by answer
left join (select count(*) FROM fast_survey as totalCount) as all on true

Find relative percentage

4 Answers4