0

I am trying to get the total amount of money which the user spent between a certain period. The code that I have come up with can successfully get the result but I also want to show the users who didn't spend any money on that certain period.

The code I have come up with:

select guest_id,
    sum(spent)
from shopping_t  
left join 
(
    SELECT ADDDATE( CURDATE() - INTERVAL 3 MONTH, INTERVAL @i:=@i+1 DAY) AS days
    FROM (
        SELECT a.a
        FROM (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
        CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
        CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c
    ) a
    JOIN (SELECT @i := -1) r1
    WHERE 
    @i < DATEDIFF(NOW(), NOW() - INTERVAL 3 MONTH)
) as target
on shopping_date = target.days 
where shopping_date = target.days
group by guest_id

The code above gives me this result:

id  spent
0   220000
2   10000

My desired output is:

id  spent
0   220000
1   0 <----- I also want to display users who didn't suspend anything on that period
2   10000

guest_t table:

id  name    address     telphone    
0   George  Summerwind  042968084       
1   Lisa    Laplace     042968084       
2   Anne    Windhill    042968084       
3   Mike    Fairview    042168084       

shopping_t table:

id  spent   date
0   150000  2016-03-02
0   50000   2016-01-03
0   20000   2016-02-16
1   1500    2014-01-02
1   3400    2014-01-03
2   9000    2016-01-02
2   1000    2016-01-03

I have tried crossing date list query and guest_t and left join to shopping_t but I can't figure out what is wrong: http://sqlfiddle.com/#!9/57f9d/3

2

Because you want to see every single user, table guest_t should be the main table. You can then LEFT JOIN shopping_t to it for existing shopping records.

This query use the period from 01-01-2016 to 01-04-2016. It seems to be what has been used for your desired output. I remove the calendar query because it does not seem required in your desired output.

Query:

SELECT gt.name, gt.id, coalesce(sum(st.money), 0) 
FROM guest_t AS gt
LEFT JOIN shopping_t AS st
    ON gt.id = st.guest_id
        AND st.shopping_date >= '2016-01-01' 
        AND st.shopping_date < '2016-04-01'
GROUP By gt.id, gt.name;

Coalesce replace NULL by 0.

Output is similar to your desired output:

   name | id | coalesce(sum(st.money), 0) 
 george |  0 |                     220000 
   Lisa |  1 |                          0 
   Anne |  2 |                      10000 
   Mike |  3 |                          0 

If you want data for the past 3 months starting NOW(), the calendar subquery is not needed. You only need to check the boundaries (NOW and NOW - 3 months) in the ON clause:

SELECT gt.name, gt.id, coalesce(sum(st.money), 0) 
FROM guest_t AS gt
LEFT JOIN shopping_t AS st
    ON gt.id = st.guest_id
        AND st.shopping_date >= (CURDATE () - INTERVAL 3 MONTH)
        AND st.shopping_date < CURDATE ()
GROUP By gt.id, gt.name;

This query gives the same output.

Both query are in this SQL Fiddle.

Your query with dates in the WHERE clause didn't work because a LEFT JOIN returns NULL for rows with no match in the shopping table. NULL cannot be compared to the dates and the result of the condition is false. Therefore the whole row (guest+shooping) is discarded by the WHERE clause:

| id |   name |       city | address |       tel | point | etc | guest_id |  money |              shopping_date 
|  0 | george | Summerwind |         | 042968084 |  -100 |     |        0 | 150000 |    March, 02 2016 00:00:00 
|  0 | george | Summerwind |         | 042968084 |  -100 |     |        0 |  50000 |  January, 03 2016 00:00:00 
|  0 | george | Summerwind |         | 042968084 |  -100 |     |        0 |  20000 | February, 16 2016 00:00:00 
|  2 |   Anne |   Windhill |         | 042968084 |  -100 |     |        2 |   9000 |  January, 02 2016 00:00:00 
|  2 |   Anne |   Windhill |         | 042968084 |  -100 |     |        2 |   1000 |  January, 03 2016 00:00:00 
|  1 |   Lisa |    Laplace |         | 042968084 |     0 |     |   (null) | (null) |                     (null) 
|  3 |   Mike |   Fairview |         | 042168084 |     0 |     |   (null) | (null) |                     (null) 

From this output, the last 2 row are discarded.

When the date condition is move to the ON clause, shopping_t is checked before the LEFT JOIN. If the condition is not true, they are not used in the JOIN and replaced by NULL. However because a LEFT JOIN guest_t data stay in place whether shopping_t returns nothing (NULL).


If you want to see each days whether there is data or not, you have to CROSS JOIN the calendar subquery with guest_t and then use a LEFT JOIN:

select guest_id, t.days
    coalesce(sum(money), 0)
from (
    SELECT ADDDATE...
) as t
CROSS JOIN guest_t gt
left join shopping_t  
    on gt.id = st.guest_id
        AND shopping_date = t.days 
group by guest_id, t.days;

I remove the final WHERE clause with is similar to ON clause.

  • thank you. it solved my problem .i made an exact similar query yesterday except the date condition is inside the where clause . can you explain why it didnt work? – ueharajohji Mar 10 '16 at 0:19
  • I have added an explaination – Julien Vavasseur Mar 10 '16 at 9:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.