try this SELECT client_name, COUNT(sale.client_id) as total FROM client, sale WHERE client.client_id *= sale.client_id GROUP BY client_name ORDER BY total DESC
nandar ----- Original Message ----- From: <[EMAIL PROTECTED]> To: <php-db@lists.php.net> Sent: Wednesday, July 06, 2005 1:11 PM Subject: [PHP-DB] Question about COUNT(*) > 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 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
