CODEWITHSUNDEEP
mysqlsqlcoalesce
timw07
timw07

Reputation: 339

COALESCE w/ mySQL when Vaue is NULL

I have the following sql which should return a sum of all values in tbl_userbets or 0 (if Null) for a given user.

select userid, COALESCE(SUM(bet),0) as Sum from `tbl_userbets`
where userid in (1289, 1305, 1315) and graded is not null
group by userid
order by userid

IDs 1289 and 1305 have no value in tbl_userbets, I'd expect a return of the following

userid | Sum
1289   | 0
1305   | 0
1315   | 1000

But I'm only getting the return of 1315.

Any help is appreciated.

Upvotes: 1

Views: 154

Answers (2)

Joel Coehoorn
Joel Coehoorn

Reputation: 416149

First of all, you have one expression inside out. Change this:

COALESCE(SUM(bet),0)

to this:

SUM(COALESCE(bet,0))

If you don't do this, a NULL for any value for a user in table will result in 0 as the entire sum for the record.

As for why the records are missing entirely, there are probably no records at all for them in the bets table. Using an IN() expression won't make those results appear. You still need some record to match with. To make this happen, JOIN with a table that is guaranteed to have a record for every user in your database.

Upvotes: 0

Dan Bracuk
Dan Bracuk

Reputation: 20804

Something like this should work.

select u.userid
, COALESCE(SUM(bet),0) as Sum 
from tbl_users u left join tbl_userbets ub on u.userid = ub.userid
and ub.graded is not null
where u.userid in (1289, 1305, 1315) 
group by u.userid
order by u.userid

You'll have to use the correct tablename though. I just guessed.

Upvotes: 1

Related Questions