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

>>>>> "Janning" == Janning Vygen <vygen(at)planwerk6(dot)de> writes:

Janning> I need some help please,
Janning> i am having two simple tables:

Janning> # select * from sales;
Janning> client_id | product
Janning> -----------+---------
Janning> 1 | toolbox
Janning> 1 | nails
Janning> 2 | nuts

Janning> # select * from clients;
Janning> id | name
Janning> ----+-------
Janning> 1 | peter
Janning> 2 | john
Janning> 3 | marc

Janning> now i want to show all client name and the count of the sales in one
Janning> table like this:

you need a left join...

merlyntest=# select name, count(sales.client_id)
merlyntest-# from clients left join sales on clients.id = sales.client_id
merlyntest-# group by name;
name | count
-------+-------
john | 1
marc | 0
peter | 2
(3 rows)

--
Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095
<merlyn(at)stonehenge(dot)com> <URL:http://www.stonehenge.com/merlyn/>
Perl/Unix/security consulting, Technical writing, Comedy, etc. etc.
See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!