Re: showing also value '0' with aggregate count()
От | Janning Vygen |
---|---|
Тема | Re: showing also value '0' with aggregate count() |
Дата | |
Msg-id | 01092809275901.12397@janning обсуждение исходный текст |
Ответ на | showing also value '0' with aggregate count() (Janning Vygen <vygen@planwerk6.de>) |
Ответы |
Re: showing also value '0' with aggregate count()
|
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: