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

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

--
Planwerk 6 /websolutions
Herzogstrae 86
40215 Dsseldorf

fon 0211-6015919
fax 0211-6015917
http://www.planwerk6.de