In the course of using MySQL the following issue has come up a few times; I
am unaware of the solution for it, but I am sure one exists, since it seems
fairly elementary. It has to do with the COUNT(*) and GROUP BY functions.
Let's say I have clients, and clients buy items.
table clients:
---------------------------------
ClientID | ClientName
---------------------------------
1 | John
2 | Mary
--------------------------------
table items:
---------------------------------------------------
ClientIDWhoBoughtIt | ItemName
---------------------------------------------------
1 | Dishwasher
1 | Light Bulb
1 | Grill
---------------------------------------------------
Notice that John has bought three things, and Mary did not buy anything.
Now, I want a report on how many items each client has bought. The way you
are supposed to do this is:
SELECT ClientName, COUNT(*) AS itemcount FROM clients, items WHERE
clients.ClientID = items.ClientIDWhoBoughtIt GROUP BY clients.ClientID
This will return:
--------------------------------------------------
ClientName | itemcount
--------------------------------------------------
John | 3
--------------------------------------------------
Which is nice, but is not what I want, because I don't get any information
about Mary. What I want, obviously, is:
--------------------------------------------------
ClientName | itemcount
--------------------------------------------------
John | 3
Mary | 0
--------------------------------------------------
Is there any way to get that result? I would think there must be. Such
functionality is vital if, say, you want to build a report with an
alphabetical list of everyone who's signed up for your web site, and the
number of things they've bought. It doesn't make sense to not list their
names simply because they signed up and didn't buy anything yet - that may
be valid information.
I would greatly appreciate anyone's input into this.
Sincerely,
Ray Prisament
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
