Re: showing also value '0' with aggregate count()

Am Donnerstag, 27. September 2001 19:01 schrieb David Christian:

> > btw: intersting point is, that the solution with union doesnt
> > work when client_id might be null.
> >
> > dont knwo why ...
>
> In this case, use "select all". Though, I advise against null
> fields in this table. Something like this would be better:
>
> create table sales (client_id integer default 0 not null, product
> text);
>
> That way you always have a "known" value. The value 0 (or whatever
> else you choose) means there is no client id associated with the
> product (or sale or whatever it is you are doing).

ok this is a good hint!

> Anyway, here's the statement that will work in your case:
>
> select name, count(client_id) from sales, clients where client_id =
> id group by name UNION select name,0 from clients where not id in
> (select all client_id from sales) order by 2;

'ALL' is the default, so it doesn't change anything.
it still dont work with NULL values.

here are my tables with a NULL value in the client_id field.

# select * from sales;
client_id | product
-----------+----------
1 | toolbox
1 | nails
2 | nuts
| junkfood

# select client_id from sales;
client_id
-----------
1
1
2

# select * from clients;
id | name
----+-------
1 | peter
2 | john
3 | marc

# select name,0 from clients where not id in (select all client_id
from sales) order by 2;
name | ?column?
------+----------
(0 rows)

THE FOLLOWING STATEMENT WORKS! WHY????

#select name,0 from clients where not id in (select all client_id
from sales where client_id is not null) order by 2;
name | ?column?
------+----------
marc | 0
(1 row)

Whats the difference?? when i ask for rows with "not id in" and my id
is 3 and the subquery returns a NULL but no '3' than '3 is in NULL'
???

Look at this:

select 'funny' where not 3 in (NULL);
?column?
----------
(0 rows)

3 is not in NULL!!

testdb=# select 'funny' where not 3 in ( 2, 1);
?column?
----------
funny
(1 row)

Janning