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

>>>>> "Janning" == Janning Vygen  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
 
Perl/Unix/security consulting, Technical writing, Comedy, etc. etc.
See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!