Re: 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