CODEWITHSUNDEEP
mysqlcoalesce
timw07
timw07

Reputation: 339

collesce issue with mysql

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

Upvotes: 0

Views: 77

Answers (2)

sgeddes
sgeddes

Reputation: 62861

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271151

This is your query:

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;

The filter in the where clause finds no rows that match for user id 2280. Assuming that at least one row exists somewhere, you can get what you want by moving the date comparison to a conditional aggregation:

SELECT userid,
       sum(case when date > DATE_SUB(NOW(), INTERVAL 30 day)
                then netAmount else 0
           end) as Sum
FROM `tbl_values`
WHERE userid in (2280, 399, 2282)
GROUP BY userid;

EDIT:

If you really want all three results, then use a left join:

SELECT u.userid,
       coalesce(sum(netAmount), 0) as Sum
FROM  (select 2280 as userid union all
       select 399 union all
       select 2282
      ) u left join
      tbl_values t
      on u.userid = t.userid and
         t.date > DATE_SUB(NOW(), INTERVAL 30 day)
GROUP BY u.userid;

Upvotes: 0

Related Questions