Re: count( only if true)

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(at)goldfisch(dot)at>
To: Martín Marqués <martin(at)bugs(dot)unl(dot)edu(dot)ar>, PostgreSQL List <pgsql-general(at)postgresql(dot)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(at)goldfisch(dot)at
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
------- End of Original Message -------