mysql left join problem with SUM and WHERE clause

Question

I have 2 tables in my database: item and category. Items can be active, or inactive, and have a categoryID that relates to the id of a record in the category table.

i want to perform a query to show all the categories, with the total cost of active items for the category

So my goal is to return something looking like this:

    +--------+------------+---------------+
    | id     | cat_name   | total_cost    |
    +--------+------------+---------------+
    |      1 | cat 1      | 12            |
    |      2 | cat 2      | 0             |
    |      3 | cat 3      | 45            |
    +--------+------------+---------------+

My first query:

    SELECT a.*, 
    SUM(b.cost) AS total_cost
    FROM categories a LEFT JOIN items b
    ON(a.id = b.category_id)
    GROUP BY a.category_name

works ok, but it returns NULL items instead of 0, and uses all items regardless of active/inactive:

    +--------+------------+---------------+
    | id     | cat_name   | total_cost    |
    +--------+------------+---------------+
    |      1 | cat 1      | 44            |
    |      2 | cat 2      | NULL          |
    |      3 | cat 3      | 87            |
    +--------+------------+---------------+

my second query adresses the NULL values:

    SELECT a.*, 
    SUM(IF(b.cost IS NULL, 0, b.cost)) AS total_cost
    FROM categories a LEFT JOIN items b
    ON(a.id = b.category_id)
    GROUP BY a.category_name

and turns out like so:

    +--------+------------+---------------+
    | id     | cat_name   | total_cost    |
    +--------+------------+---------------+
    |      1 | cat 1      | 44            |
    |      2 | cat 2      | NULL          |
    |      3 | cat 3      | 87            |
    +--------+------------+---------------+

So in my tiny useless brain i try the following query, adding a WHERE clause on table b where active has to = 1 (true)

    SELECT a.*, 
    SUM(IF(b.cost IS NULL, 0, b.cost)) AS total_cost
    FROM categories a LEFT JOIN items b
    ON(a.id = b.category_id)
    WHERE b.active = 1 
    GROUP BY a.category_name

and i get the following:

    +--------+------------+---------------+
    | id     | cat_name   | total_cost    |
    +--------+------------+---------------+
    |      1 | cat 1      | 12            |
    |      3 | cat 3      | 45            |
    +--------+------------+---------------+

so as you can se, i would like to return the entire range of categories, even when the right table returns no matching results... Any takes for a million imaginary cool points?

Answer 1

Try this:

  SELECT a.*,  
    SUM(Case B.Active When 1 Then b.cost else 0 End) AS total_cost 
    FROM categories a 
       LEFT JOIN items b 
         ON b.category_id = a.id  
    GROUP BY a.category_name 

or this:

    SELECT a.*, SUM(b.cost) AS total_cost 
    FROM categories a 
       LEFT JOIN items b 
         ON b.category_id = a.id 
            And B.Active = 1 
    GROUP BY a.category_name 

Answer 2

Use:

   SELECT c.id,
          c.cat_name,
          COALESCE(SUM(i.cost), 0) AS total_cost
     FROM CATEGORIES c
LEFT JOIN ITEMS i ON i.category_id = c.category_id
                 AND i.active = 1
 GROUP BY c.id, c.cat_name