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?
-----------------------
| client_name | total |
-----------------------
| John | 3 |
| Mark | 2 |
| Luke | 1 |
| Matthew | 0 |
-----------------------
I've tried: SELECT client.name, COUNT(*) total
FROM client, sales
WHERE client.client_id = sales.client_id
GROUP BY client.client_name
ORDER BY total DESC
and it returns:
-----------------------
| client_name | total |
-----------------------
| John | 3 |
| Mark | 2 |
| Luke | 1 |
-----------------------
How can I have Matthew who does't purchase anything on the list?
Your help is much appreciated. Thanks.
Erick Wellem
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
