showing also value '0' with aggregate count()

From: Janning Vygen <vygen(at)planwerk6(dot)de>
To: "PostgreSQL-General" <pgsql-general(at)postgresql(dot)org>
Subject: showing also value '0' with aggregate count()
Date: 2001-09-27 14:51:45
Message-ID: 01092716514505.27858@janning
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David Christian 2001-09-27 15:18:19 Re: showing also value '0' with aggregate count()
Previous Message Szabo Zoltan 2001-09-27 14:30:01 Re: CREATION OF PRIMARY KEYS