CODEWITHSUNDEEP
mysqlsumleft-join
Mike Feng
Mike Feng

Reputation: 833

MySQL SUM left join show all categories even with the sum of 0

I wish to select all categories based on the sum of scores of a set of data, even if the set of data does not include the categories. However, it always only display categories with actual data no matter what I try.

I have created the following SQLFiddle http://sqlfiddle.com/#!9/52a127/3

For ease of view, I'll paste the select statement here:

SELECT categories.id, IFNULL(SUM(raw_data.score), 0) as total
FROM categories
LEFT JOIN raw_data ON categories.id = raw_data.category_id
WHERE
(raw_data.quarter = '2018Q2' OR !raw_data.quarter) AND
raw_data.broker_id = 2 
GROUP BY categories.id
ORDER BY total DESC

As you can see from the fiddle, it only displays 2 categories, but I wish to select all 6 and have 0 for those with no results.

Any help is appreciated, thanks!

Upvotes: 1

Views: 25

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175636

You need to move condition from WHERE to ON clause:

SELECT categories.id, IFNULL(SUM(raw_data.score), 0) as total
FROM categories
LEFT JOIN raw_data ON categories.id = raw_data.category_id
  AND (raw_data.quarter = '2018Q2' OR !raw_data.quarter) 
  AND raw_data.broker_id = 2 
GROUP BY categories.id
ORDER BY total DESC;

SQLFiddle Demo

Upvotes: 3

Related Questions