Return null value instead of the empty set in sql query

Question

Let's say, there are two tables:

select * from users;
+-------+------+
| login | type |
+-------+------+
| test1 | A    |
| test2 | A    |
| test3 | B    |
+-------+------+

and

select * from money;
+-------+--------+------------+
| login | amount | date       |
+-------+--------+------------+
| test1 |  10.00 | 1325379661 |
| test1 |  20.00 | 1357002061 |
| test2 |  33.30 | 1388538061 |
+-------+--------+------------+

I need to select login&type fields from the users table and SUM of the amount field from the second table within the selected date range. If sum is empty for the selected period or there are no any records for a login in the 'money' table, then return zero value (not empty set).

I'm doing the following way:

SELECT u.login,u.type, SUM(m.amount) as amount 
FROM users AS u 
LEFT JOIN money AS m ON m.login=u.login 
WHERE date>1388710861 GROUP BY u.login;

This query reasonably returns empty set but I need to get the list of users and zero amount for them:

+-------+------+--------+
| login | type | amount |
+-------+------+--------+
| test1 | A    |  0.00  |
| test2 | A    |  0.00  |
| test3 | B    |  0.00  |
+-------+------+--------+

How is that possible?

The result I'm trying to get is already posted. Please take a look. — Sray, Oct 12 '14 at 14:49
Look at this: http://stackoverflow.com/questions/7602271/how-do-i-get-sum-function-in-mysql-to-return-0-if-no-values-are-found — Multisync, Oct 12 '14 at 14:52
I've already tried COALESCE. No luck, the same empty set is returned. My case is complicated by using multiple joins and I guess the correct way lies within altering the way the table is joined. I tried multiple variants already, even subqueries, but got the same result. Also, I've already searched for the similar case Stackoverflow, but did not found any. — Sray, Oct 12 '14 at 14:58

score 2 · Accepted Answer · answered Oct 12 '14 at 15:00

Its empty because your where condition (date>1388710861) filter all data

try this one

SELECT u.login,u.type, SUM(IF(m.date>1388710861,m.amount,0)) as amount 
FROM users AS u 
LEFT JOIN money AS m ON m.login=u.login 
GROUP BY u.login, u.type;

Sure, it will run slower.

score 0 · Answer 2 · answered Oct 15 '14 at 02:11

Look at your data, No one data with your criteria ( date>1388710861 ). actually, column amount always 0.

this is standart query to get sum amount of each login.

SELECT u.login,u.type, SUM(m.amount) as amount 
FROM users AS u 
LEFT JOIN money AS m ON m.login=u.login 
GROUP BY u.login, u.type;

but, if you want to get result, sum amount of each login in case ( date>1388710861 ) will preview real sum amount and in case ( date<=1388710861 ) will preview 0

you can try this :

SELECT u.login,u.type,
    Sum(Case When date>1388710861 Then m.amount Else 0 End) as amount
FROM users AS u 
LEFT JOIN money AS m ON m.login=u.login 
GROUP BY u.login, u.type;

Return null value instead of the empty set in sql query

2 Answers2