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