Thread: Counts and percentages and such
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
On Tue, Dec 8, 2009 at 11:50 AM, jackassplus <jackassplus@gmail.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.
<snip> > 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: What does ::numeric signify? I'm using the jdbc driver from http://jdbc.postgresql.org/ in SQuirreL and it asks me for the value of :numeric.
On Tue, Dec 8, 2009 at 2:21 PM, jackassplus <jackassplus@gmail.com> wrote: > <snip> >> 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: > > What does ::numeric signify? > I'm using the jdbc driver from http://jdbc.postgresql.org/ in SQuirreL > and it asks me for the value of :numeric. ::numeric is a cast in pgsql. The long version looks like: cast (col as numeric) which might work better. I'm no expert on the jdbc driver so your question may require someone else to give a definitive answer.
Btw, Squirrel sucks, I tried using it in my last job and it got in the way more than it helped with pgsql. I just use psql or pgadmin III if I need a gui.
jackassplus wrote on 08.12.2009 22:21: > What does ::numeric signify? > I'm using the jdbc driver from http://jdbc.postgresql.org/ in SQuirreL > and it asks me for the value of :numeric. As Scott has pointed out this is a typecast. If Squirrel mistakes that for a parameter, it's clearly a bug in Squirrel. But maybe that prompting for parameters can be turned off somewhere (I don't use Squirrel, so I cannot tell) Thomas
On Tue, Dec 8, 2009 at 3:09 PM, Thomas Kellerer <spam_eater@gmx.net> wrote: > jackassplus wrote on 08.12.2009 22:21: >> >> What does ::numeric signify? >> I'm using the jdbc driver from http://jdbc.postgresql.org/ in SQuirreL >> and it asks me for the value of :numeric. > > As Scott has pointed out this is a typecast. > If Squirrel mistakes that for a parameter, it's clearly a bug in Squirrel. > But maybe that prompting for parameters can be turned off somewhere (I don't > use Squirrel, so I cannot tell) My experience with Squirrel was that it worked fine for very simple queries, and as soon as you got outside the box it started doing the stuff the OP is seeing. For postgresql the preferred GUI is pgadmin III, but psql is the best text only interface for a db on the planet.
> My experience with Squirrel was that it worked fine for very simple > queries, and as soon as you got outside the box it started doing the > stuff the OP is seeing. For postgresql the preferred GUI is pgadmin > III, but psql is the best text only interface for a db on the planet. > I'm just using squirrel to test queries prior to throwing them into perl. Thanks everybody for the help.
> jackassplus <jackassplus@gmail.com> :
> I'm just using squirrel to
Sure!
But it's bad.
--
Architecte Informatique chez Blueline/Gulfsat:
Administration Systeme, Recherche & Developpement
+261 34 29 155 34 / +261 33 11 207 36