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

>>>>> "Janning" == Janning Vygen <vygen@planwerk6.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@stonehenge.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!