SELECT COUNT JOIN GROUP BY issue not showing all empty counts

[soycharliente]

Hello! I'm trying to display a list of clients and count the number of items they have in another table including clients that have a count of 0.

 

The structure of my two tables looks like this:

clients
----------
id
name

items
----------
id
client_id
item_id

The query I have so far is:

SELECT c.*, IFNULL(COUNT(i.item_id), 0) as numItems FROM clients c LEFT JOIN items i on c.id = i.client_id GROUP BY i.client_id ORDER BY c.name

I'm close, but it stops at the first client with 0 items and doesn't show anymore clients. If that client with 0 items finally gets some items, the next client with 0 items appears in the list. I'm not sure what I'm missing. This is the closest I've gotten and I'm now stumped from reading around here and SO.

 

Thanks for a fresh pair of eyes.

[Jacques1]

You're grouping by the item's client ID which is NULL whenever a client has no items. You need to group by c.id.

[Psycho]

You also don't need the IFNULL() function. The COUNT() function will take care of the NULL records. and return 0 (if there are none). Also I would normally COUNT() the foreign key field in the JOINed table in this type of scenario.

SELECT c.*, COUNT(i.client_id) as numItems
FROM clients c
LEFT JOIN items i
  ON c.id = i.client_id
GROUP BY c.id
ORDER BY c.name

Edited July 7, 2017 by Psycho

[soycharliente]

You're grouping by the item's client ID which is NULL whenever a client has no items. You need to group by c.id.

 

Ahhhh. I am teaching myself JOIN and I found example code that I tried to rewrite. That totally makes sense.

 

 

You also don't need the IFNULL() function. The COUNT() function will take care of the NULL records. and return 0 (if there are none). Also I would normally COUNT() the foreign key field in the JOINed table in this type of scenario.

SELECT c.*, COUNT(i.client_id) as numItems
FROM clients c
LEFT JOIN items i
  ON c.id = i.client_id
GROUP BY c.id
ORDER BY c.name

 

The IFNULL part was something I read on SO and they said it was needed. I couldn't ever get 0 to show up when I used COUNT alone. Maybe I made more than one change at the same time without realizing and adding the INFULL combined with some other change got it working. When I added it the 0 counts started showing up. I still don't fully understand what the IFNULL part was doing, but my code isn't live yet and I was still trying to understand it before pushing it out. Guess I don't have to worry about that now. lol. Thanks for this rewrite.

Edited July 7, 2017 by charlieholder

[Jacques1]

The whole truth is that your query isn't even valid SQL. You cannot select columns which aren't listed in the GROUP BY clause (and aren't functionally dependent on any of the columns), because the result is ambiguous. For example, this is what PostgreSQL says when trying to execute your query:

ERROR:  column "c.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT c.*, COUNT(i.id) as numItems FROM clients c LEFT JOIN...
               ^

To make the problem more obvious, take the following table and query:

first_name  | last_name | address_id
------------+-----------+-----------
Joe         | Blow      | 1
Jane        | Blow      | 1

SELECT
    first_name,
    last_name,
    COUNT(*)
FROM
    users
GROUP BY
    address_id

You're counting the users per address, but you're also asking for the first and last name corresponding to each address. What is that supposed to be? There are two users living at the same address, so it's impossible to tell.

 

Any proper database system and MySQL with a strict configuration immediately reject those kinds of queries. MySQL in sloppy mode allows them under the assumption that you know what you're doing (or just don't care), but of course that means you may run into weird effects at any time. I wouldn't recommend this. Fix your MySQL configuration and only write valid SQL queries. That means not referencing columns unless they're in the GROUP BY clause.

 

There's an exception to this rule in newer revisions of the SQL standard: You may also reference columns which are functionally dependent on the columns in the GROUP BY clause. For example, when you group by the primary key of particular table, then you may select any column from that table, because the value is unambiguous. However, this requires a recent MySQL version (at least 5.7.5), so make sure you test this on your server.