Counts and percentages and such

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