Count on this LEFT OUTER query is returning 1 when I want 0

Question

I have the following query:

SELECT 
    l.id, l.name, l.store_id, COUNT(1) as reviewCount
FROM
    locations l
LEFT OUTER JOIN
    reviews r ON l.id=r.location_id
GROUP BY 
    l.id 
ORDER BY
     reviewCount DESC

I have 2 tables; locations, reviews. Reviews stores location_id. I want to get all locations and include the reviewCount for each of the locations. When there is a location that doesn't have any reviews I get a reviewCount of 1. But I want to get a reviewCount of 0 for those locations.

Thanks for the help!

Join the argument: http://stackoverflow.com/questions/1221559/count-vs-count1 — SirRupertIII, Dec 10 '16 at 21:21

score 2 · Accepted Answer · answered Dec 10 '16 at 21:15

You want to count the matches in the second table, so count a column from that table:

SELECT l.id, l.name, l.store_id, COUNT(r.location_id) as reviewCount
. . .

What happens is that COUNT(1) is counting the rows being returned. A row is being returned with NULL values from the second table.

Count on this LEFT OUTER query is returning 1 when I want 0

1 Answers1