[EMAIL PROTECTED] wrote: > Hi, > > Let's say that I have 2 tables: client and sales > --------------------------- > | client_id | client_name | > --------------------------- > | 1 | John | > | 2 | Mark | > | 3 | Luke | > | 4 | Matthew | > --------------------------- > > ------------------------------------- > | sale_id | client_id | sale_item | > ------------------------------------- > | 1 | 1 | Book | > | 2 | 1 | Pencil | > | 3 | 1 | Pen | > | 4 | 2 | Ruler | > | 5 | 2 | Bag | > | 6 | 3 | Hat | > ------------------------------------- > > How can I have total purchased items for each of the client like this table > below since COUNT(*) is only for non-NULL values? <snip>
You could also try something like this: SELECT client.name, IFNULL(COUNT(sales.sale_id), 0) AS total FROM client LEFT JOIN sales ON client.client_id = sales.client_id GROUP BY client.name ORDER BY total DESC The left join forces every record in the left table of the join (client) to be present in the result set whether or not there are matching entries in the right table (sales). The IFNULL takes care fo your count problem. HTH Rory -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
