mySQL question on how to add more info to a column

Got this table containing info about customers, dates and their purchases values. Let’s say some of them bought something in November but nothing in December. Now, I am trying to have all the customers + all they spent and to use IFNULL() to the ones that have not bought anything in Dec. I am filtering it for Dec and all I get is the ones that purchased something in Dec, yeah… I know but I am sure there’s a way how to do that but I just can’t figure it out. Any help is more than welcome. Cheers

Customers    Date           Spent
John1        2000-11-01     12
John2        2000-11-02     33
John3        2000-11-03     13
John4        2000-11-04     24
John5        2000-11-05     36
John6        2000-12-01     55
John7        2000-12-02     16
John8        2000-12-04     33
John9        2000-12-03     18
John10       2000-12-03     13

Answer

You can enumerate the customers in a subquery, then bring the december records with a left join:

select c.customers, coalesce(sum(t.spent), 0) total_spent
from (select distinct customers from mytable) c
left join mytable t 
    on  t.customers = c.customers
    and t.date >= '2000-12-01'
    and t.date <  '2001-01-01'
group by c.customers

This gives you one row per customer, with their total spent in December 2000 – or 0 if they did not spent during that month.

In a real-life situation, you would typically have a separate table to store the customers, that you would use instead of the subquery.