CODEWITHSUNDEEP
mysql
Jacob Kranz
Jacob Kranz

Reputation: 951

mysql return count = 0 without joins using group by

I have a table

Image
| ImageId | UserId | SourceId |
| 1       | 1      | 1        |
| 2       | 1      | 2        |
| 3       | 2      | 1        |
| 4       | 2      | 2        |
| 5       | 3      | 1        |
| 6       | 3      | 1        |
| 7       | 3      | 1        |
| 8       | 3      | 1        |
| 9       | 3      | 1        |

I then have a query:

SELECT UserId, IFNULL( COUNT( ImageId ) , 0 ) AS ImageCount, SourceId
FROM Image
GROUP BY UserId, SourceId

When I do the query, I get

| UserId | SourceId | ImageCount
| 1      | 1        | 1
| 1      | 2        | 1
| 2      | 1        | 1
| 1      | 2        | 1
| 3      | 1        | 5

However, the one row that I do NOT get back (which I want) is:

| 3      | 1        | 0

How do I go about fetching the row, even if the count is 0?

All of the questions I've seen have had to deal with joins (usually left joins) but as this doesn't require a join I'm a little confused as to how to go about this.

Upvotes: 0

Views: 34

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

This does require a left join and a bit more. You need to start with all possible combinations, then use left join to bring in the existing values:

SELECT u.UserId, COUNT(i.ImageId ) AS ImageCount, s.SourceId
FROM (select distinct UserId from Image) u cross join
     (select distinct SourceId from Image) s left join
     Image i
     on i.UserId = u.UserId and i.SourceId = s.SourceId
GROUP BY u.UserId, s.SourceId;

The count() will return 0 if there are no matches. There is no need for an if, coalesce, or case statement.

What this does is create every possible combination of UserId and SourceId (based on the values in the Image table). It then uses a left outer join to connect back to the image. What does this do? Well, for existing records, it actually does nothing. But for combinations that don't appear in the table, it will add a new row with u.UserId, s.SourceId, and NULL in the other fields. This is the basis for the final aggregation.

Upvotes: 3

Related Questions