On Tue, Dec 8, 2009 at 11:50 AM, jackassplus <jackassplus@xxxxxxxxx> 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
