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

Hmmm... I'm using 7.1.2, and don't know if you should upgrade or not.

Can you use UNIONs?

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 client_id
from sales) order by 2;

Yes, when I said "Try a left join" I should have said "left outer
join"...the "OUTER" is commonly omitted. Thanks. :)

Note that with UNION, you refer to columns by number, hence "order by 2".

David

On 9/27/01 11:35 AM, "Janning Vygen" <vygen(at)planwerk6(dot)de> wrote:

> Am Donnerstag, 27. September 2001 17:18 schrieb David Christian:
>> 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;
>
> # select name, count(client_id) AS cnt from clients left join sales on
> (client_id = id) group by name order by cnt;
>
> ERROR: OUTER JOIN is not yet supported
> (PostgreSQL) 7.0.3
>
> do i need to upgrade or is there a workaround??
>
>> 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. :)
>
> can you read my mind?? I am looking for a really good book on SQL. i
> will buy this and we'll see how tired i will be tomorrow..
>
> thanks
> janning
>
>> 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