At 7:40 PM -0400 9/18/01, Raymond Prisament wrote:
>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
Which will return results only for rows where a match can be found in
both tables.
>
>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
>--------------------------------------------------
Which means you want a result even for clients that are missing
in the item table. Aha, missing rows. That means a LEFT JOIN.
SELECT ClientName, COUNT(items.ClientIDWhoBoughtIt) AS itemcount
FROM clients LEFT JOIN items ON clients.ClientID = items.ClientIDWhoBoughtIt
GROUP BY clients.ClientID
So the problem isn't really your GROUP BY. The trick is to realize
that you want to join to produce a row for every clients row, even
when there's no match in the other table. LEFT JOIN does this. It
produces a row with the clients columns filled in and the items columns
set to NULL.
But you also need to change COUNT(*) to COUNT(items.ClientIDWhoBoughtIt)
because COUNT(*) counts all rows where as COUNT(col_name) counts only
non-NULL values.
>
>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
--
Paul DuBois, [EMAIL PROTECTED]
---------------------------------------------------------------------
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
