Aamir Khan Aamir Khan - 6 months ago 16
SQL Question

What is the best way to use Total to get percentage of groups in PostgreSQL

In excel and other similar software you can use total to get the percentage. Can anyone tell what is the most efficient way to replicate total function.

I have used nested query but I am not getting right result

select retpre04recency,
count(*) as CustomerCount,
(select count(*) from extractsummary) as Total,
round(count(*)/(select count(*) from extractsummary),2) as CustomerCount
from extractsummary
group by retpre04recency
order by retpre04recency asc
;


My result in percentage column is zero. can anyone help?

enter image description here

Answer

This is a type problem. The expression

count(*)

results in type bigint. The expression

(select count(*) from extractsummary)

also results in type bigint. Unlike some programming languages (e.g. R), the division operator in PostgreSQL does not automatically promote integer operands to a fractional type. So you must cast it yourself.

select
    retpre04recency,
    count(*) as CustomerCount,
    (select count(*) from extractsummary) as Total,
    round(count(*)::numeric/(select count(*) from extractsummary),2) as CustomerCount
from
    extractsummary
group by
    retpre04recency
order by
    retpre04recency asc
;

Example:

drop table if exists extractsummary;
create table extractsummary (retpre04recency int);
insert into extractsummary (retpre04recency) values (1), (1), (2), (2), (2), (3), (3), (3), (3), (4), (4), (4), (5), (5), (5), (5), (5), (6), (6), (6), (99);

select
    retpre04recency,
    count(*) as CustomerCount,
    (select count(*) from extractsummary) as Total,
    round(count(*)::numeric/(select count(*) from extractsummary),2) as CustomerCount
from
    extractsummary
group by
    retpre04recency
order by
    retpre04recency asc
;

output

Comments