getting the total of amount of money of each user between two dates even if no record is found
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 &gt;= '2016-01-01' 
        AND st.shopping_date &lt; '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 &gt;= (CURDATE () - INTERVAL 3 MONTH)
        AND st.shopping_date &lt; 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.