Thread: count( only if true)
the count-aggreate counts every expression that does not evaluate to null. I would like to have a count that counts all values that are true. Do I really need to make a count( case when expression then 't' else null) to implement this? thnx, peter -- mag. peter pilsl goldfisch.at IT-management tel +43 699 1 3574035 fae +43 699 4 3574035 pilsl@goldfisch.at
am 12.10.2005, um 20:42:02 +0200 mailte peter pilsl folgendes: > > the count-aggreate counts every expression that does not evaluate to null. > > I would like to have a count that counts all values that are true. > > Do I really need to make a count( case when expression then 't' else null) > to implement this? count(*) from foo where bar = '...'; Regards, Andreas -- Andreas Kretschmer (Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net === Schollglas Unternehmensgruppe ===
El Mié 12 Oct 2005 15:42, peter pilsl escribió:
> the count-aggreate counts every expression that does not evaluate to null.
>
> I would like to have a count that counts all values that are true.
>
> Do I really need to make a count( case when expression then 't' else
> null) to implement this?
I'm not sure what exactly it is you want, but check this:
SELECT count(*) FROM tab WHERE expresion
There you get a count of tuples that satisfy the expresion. What NULL values
are you talking about? Can you hand an example?
--
16:17:02 up 8 days, 7:23, 1 user, load average: 0.98, 1.27, 1.27
---------------------------------------------------------
Lic. Martín Marqués | SELECT 'mmarques' ||
Centro de Telemática | '@' || 'unl.edu.ar';
Universidad Nacional | DBA, Programador,
del Litoral | Administrador
---------------------------------------------------------
Martín Marqués wrote: > > I'm not sure what exactly it is you want, but check this: > > SELECT count(*) FROM tab WHERE expresion > > There you get a count of tuples that satisfy the expresion. What NULL values > are you talking about? Can you hand an example? > thnx. # select * from test2; x | id ---+---- a | 2 b | 1 c | 4 d | 6 e | 3 e | 6 (6 rows) knowledge=# select x,count(id<5) from test2 group by x; x | count ---+------- e | 2 <---- !!!! this is unexpected b | 1 c | 1 d | 1 <---- !!!!! a | 1 (5 rows) knowledge=# select x,count(case when id<5 then 't' else null end) from test2 group by x; x | count ---+------- e | 1 <--------- thats the result I want !!! b | 1 c | 1 d | 0 a | 1 (5 rows) the problem is, that ... count(id<5) .... is the very same like ... count(id<10) ... cause count counts all values that are not null and id<5 is a boolean expression that only results in null if id is null. otherwise its 't' or 'f' which both are notnull. the where-clause is nice, but not sufficient. for example I also need queries like select x,count(id<5),count(id>15) from test2 group by x; thnx a lot, peter -- mag. peter pilsl goldfisch.at IT-management tel +43 699 1 3574035 fae +43 699 4 3574035 pilsl@goldfisch.at
try something like: select x,sum(case when id<5 then 1 else 0 end),sum(case when id>10 then 1 else 0 end from test2 group by x; ---------- Original Message ----------- From: peter pilsl <pilsl@goldfisch.at> To: Martín Marqués <martin@bugs.unl.edu.ar>, PostgreSQL List <pgsql-general@postgresql.org> Sent: Wed, 12 Oct 2005 22:24:48 +0200 Subject: Re: [GENERAL] count( only if true) > Martín Marqués wrote: > > > > I'm not sure what exactly it is you want, but check this: > > > > SELECT count(*) FROM tab WHERE expresion > > > > There you get a count of tuples that satisfy the expresion. What NULL values > > are you talking about? Can you hand an example? > > > > thnx. > > # select * from test2; > x | id > ---+---- > a | 2 > b | 1 > c | 4 > d | 6 > e | 3 > e | 6 > (6 rows) > > knowledge=# select x,count(id<5) from test2 group by x; > x | count > ---+------- > e | 2 <---- !!!! this is unexpected > b | 1 > c | 1 > d | 1 <---- !!!!! > a | 1 > (5 rows) > > knowledge=# select x,count(case when id<5 then 't' else null end) from > test2 group by x; > x | count > ---+------- > e | 1 <--------- thats the result I want !!! > b | 1 > c | 1 > d | 0 > a | 1 > (5 rows) > > the problem is, that ... count(id<5) .... is the very same like ... > count(id<10) ... cause count counts all values that are not null and > id<5 is a boolean expression that only results in null if id is null. > otherwise its 't' or 'f' which both are notnull. > > the where-clause is nice, but not sufficient. for example I also need > queries like > > select x,count(id<5),count(id>15) from test2 group by x; > > thnx a lot, > peter > > -- > mag. peter pilsl > goldfisch.at > IT-management > tel +43 699 1 3574035 > fae +43 699 4 3574035 > pilsl@goldfisch.at > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster ------- End of Original Message -------
On Wed, Oct 12, 2005 at 22:24:48 +0200, peter pilsl <pilsl@goldfisch.at> wrote: > > knowledge=# select x,count(case when id<5 then 't' else null end) from > test2 group by x; > x | count > ---+------- > e | 1 <--------- thats the result I want !!! > b | 1 > c | 1 > d | 0 > a | 1 > (5 rows) For simple cases like this you probably want to do the following: SELECT x, count(*) FROM test2 WHERE id < 5 GROUP BY x