Setting a default value on NULL columns from LEFT JOIN and filtering using WHERE

Question

I have a table of items and I have a separate table containing the individual purchases of each item. Not every item has been purchased. I am selecting the purchase count and joining it with the items table with a statement similar to the following:

SELECT items.name, purchases_count
FROM `items` 
    LEFT JOIN (SELECT sales.item_name, SUM(unit_sales) AS purchases_count 
        FROM sales GROUP BY item_name) sales ON sales.item_name = items.uid

This results in data similar to:

+------+-----------------+
| name | purchases_count |
|------+-----------------+
| Shoe | 12              |
| Belt | NULL            |
+------+-----------------+

My problem arises when I want to filter this result on the purchases_count. If I use WHERE purchases_count < 10 or something similar, items with no sales record at all (i.e. those with a NULL value) are excluded from the search.

I have attempted to use COALESCE(purchases_count,0) AS purchases_count which does correctly set NULL records to 0, but they are still not appearing when using WHERE. I suspect the COALESCE is happening after the WHERE.

I would like to be able to filter on this number using WHERE and include NULL results (as zeros) if possible. Any suggestions?

Answer 1

have u try this

SELECT items.name, IFNULL(purchases_count,0)
FROM `items` 
LEFT JOIN (
            SELECT item_name, SUM(unit_sales) AS purchases_count 
            FROM sales GROUP BY item_name
           ) s ON s.item_name = items.uid
GROUP BY items.name
HAVING purchases_count >10

Answer 2

Aliases don't generally apply in the WHERE clause - try COALESCE(purchases_count,0) AS purchases_count_2 with where purchases_count_2 < 10 to see what I mean.

where COALESCE(purchases_count,0) < 10 should work.