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@goldfisch.at