Re: Counts and percentages and such

On Tue, Dec 8, 2009 at 11:50 AM, jackassplus <jackassplus(at)gmail(dot)com> wrote:
> I'm new to both pgsql and SQL in  general pas really simple stuff, so
> i would like to know how to;
>
> Given a table with a column that can have one of NULL, (char) N,
> (char) A, and (char) L. Is there a way to in a single query, ge the
> percentage of the whole rowset that each of those represents?
>
> like :
>
> 75% Null
> 15% A
> 5% N
> 5% L

Sure. What you're looking at are aggregates and groupings.

select coalesce(col,'Null'), count(coalesce(col,'Null')) from
sometable group by col

will give you the basic counts for each one. For percentages, we do
the "part divided by the whole * 100" thing...

select coalesce(col,'Null'),
(count(coalesce(col,'Null'))::numeric/(select count(*) from
some_table))*100 from some_table group by col;
coalesce | ?column?
----------+-------------------------
Null | 13.33333333333333333300
N | 20.00000000000000000000
A | 26.66666666666666666700
L | 40.00000000000000000000

Note that it works, but we get long ugly numbers, and the column has
no name (?column?). Let's fix that:

select coalesce(col,'Null'),
((count(coalesce(col,'Null'))::numeric/(select count(*) from
some_table))*100)::numeric(12,2) as percentage from some_table group
by col;
coalesce | percentage
----------+------------
Null | 13.33
N | 20.00
A | 26.67
L | 40.00

If you want an actual percentage sign you'll have to cast to text and
add it on (or make your own type, but that seems like a lot of work
for such a simple thing):

select coalesce(col,'Null'),
(((count(coalesce(col,'Null'))::numeric/(select count(*) from
some_table))*100)::numeric(12,2))::text||'%' as percentage from
some_table group by col;
coalesce | percentage
----------+------------
Null | 13.33%
N | 20.00%
A | 26.67%
L | 40.00%

There ya go.