collesce issue with mysql

Question

I have a pretty flat table - tbl_values which has userids as well as netAmounts in a given row. In the example below, 2280 has no records in the past 30 days based on the timestamp.

I'd expect this to return 3 rows, with 2280 as "0" - but I'm only getting 2 back? Am I missing something obvious here?

SELECT userid, (COALESCE(SUM(netAmount),0)) as Sum FROM `tbl_values` where userid in (2280, 399, 2282) and date > (select DATE_SUB(NOW(), INTERVAL 30 day)) GROUP BY userid 

Answer 1

Assuming you always want to return the user, regardless of rather they have a matching record in tbl_values, what you're looking for is an outer join:

SELECT u.userid, COALESCE(SUM(v.netAmount),0) as Sum 
FROM (
    SELECT 2280 userid UNION ALL
    SELECT 399 UNION ALL
    SELECT 2282
) u 
    LEFT JOIN `tbl_values` v ON u.userid = v.userid AND
        v.date > DATE_SUB(NOW(), INTERVAL 30 day) 
GROUP BY u.userid 

---

If you perhaps have a Users table, then you can use it instead of the subquery.

SELECT u.userid, COALESCE(SUM(v.netAmount),0) as Sum 
FROM users u 
    LEFT JOIN `tbl_values` v ON u.userid = v.userid AND
        v.date > DATE_SUB(NOW(), INTERVAL 30 day) 
WHERE u.userid in (2280, 399, 2282)
GROUP BY u.userid