You need a left outer join:
select name, count(sales.client_id) from clients left outer join sales on
sales.client_id = clients.id group by name;
Outer joins were not supported prior to 7.1, so if you're using an earlier
version, you'll want to either upgrade or read the docs for your version; they
had a standard workaround prior to 7.1.
Wes Sheldahl
Janning Vygen <vygen%planwerk6.de@interlock.lexmark.com> on 09/27/2001 10:51:45
AM
To: "PostgreSQL-General" <pgsql-general%postgresql.org@interlock.lexmark.com>
cc: (bcc: Wesley Sheldahl/Lex/Lexmark)
Subject: [GENERAL] showing also value '0' with aggregate count()
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@postgresql.org so that your
message can get through to the mailing list cleanly