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
Advertisement
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.