Try a left join: select name, count(client_id) AS cnt from clients left join sales on (client_id = id) group by name order by cnt;
There's a great book for SQL called "The Practical SQL Handbook" which covers these types of problems well. Published by Addison Wesley, written by Bowman, Emerson, and Darnovsky. Good bedtime reading. :) HTH, David On 9/27/01 10:51 AM, "Janning Vygen" <[EMAIL PROTECTED]> wrote: > I need some help please, > > i am having two simple tables: > > # select * from sales; > client_id | product > -----------+--------- > 1 | toolbox > 1 | nails > 2 | nuts > > # select * from clients; > id | name > ----+------- > 1 | peter > 2 | john > 3 | marc > > now i want to show all client name and the count of the sales in one > table like this: > > # select name, count(sales.client_id) from clients, sales where > sales.client_id = clients.id group by name; > > name | count > -------+------- > john | 1 > peter | 2 > > works fine, but where is marc??? it should look like > > name | count > -------+------- > john | 1 > peter | 2 > marc | 0 > > who can i make it work??? i think i know why my select statement > doesnt work, because of the where clause marc will never join this > table because the condition will never be true... but how can i do > it?? > > i guess its very very simple, but i just cant manage it. > > thanks in advance > janning > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to [EMAIL PROTECTED] so that your > message can get through to the mailing list cleanly > ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org
