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

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" <vygen(at)planwerk6(dot)de> 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 majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>