List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:September 19 2001 12:11am
Subject:Re: Count(*) with zero rows
View as plain text  
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 <mysql-thread85815@stripped>
>To unsubscribe, e-mail <mysql-unsubscribe-paul=snake.net@stripped>
>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


-- 
Paul DuBois, paul@stripped
Thread
Count(*) with zero rowsRaymond Prisament19 Sep
  • RE: Count(*) with zero rowsScott Mebberson19 Sep
  • Re: Count(*) with zero rowsPaul DuBois19 Sep