Mysql – How to make the zero values show up in count

MySQL

I have encountered a question with the following table:

Column Name Type
user_id int
spend_date date
platform enum
amount int

The goal is to find the total number of users and the total amount spent using mobile platform only, desktop platform only and both mobile and desktop together for each date. For example, if on 2017-07-01 user '1' used mobile and desktop to make a purchase, this purchase would be accounted for "both mobile and desktop together". If user '2' used mobile to make a purchase but never used desktop, this purchase would show up in "mobile only."

My code looks like the following. There is no purchase by anyone using both mobile and desktop on 2017-07-02, thus there should be an extra row ['2017-07-02', 0,0]. However, this row does not show up. Can anyone please let me know what's wrong with my code:

with 
mobile_users as (select distinct user_id 
                 from spending 
                 where platform = 'mobile'),
desktop_users as (select distinct user_id 
                  from spending 
                  where platform = 'desktop')
(select spend_date, platform, count(user_id) as total_users, sum(amount) as total_amount 
 from spending
 where user_id not in (select user_id 
                       from mobile_users)
 group by spend_date)
union all
(select spend_date, platform, count(user_id) as total_users, sum(amount) as total_amount 
 from spending
 where user_id not in (select user_id 
                       from desktop_users)
 group by spend_date)
union all
(select spend_date, 'both' as platform, count(user_id) as total_users, sum(amount) as total_amount 
 from spending
 where user_id in (select user_id from 
                   desktop_users) 
   and user_id in (select user_id 
                   from mobile_users)
 group by spend_date)
order by spend_date

Best Answer

The thing is you are doing all pruning on Mobile or Desktop or both, you haven't add any condition for other than this values.

What I have done is other than this 3 Mobile or Desktop or both values in table, it will count the row & calculate amount if present.

with 
mobile_users as (select distinct user_id 
                 from spending 
                 where platform = 'mobile'),
desktop_users as (select distinct user_id 
                  from spending 
                  where platform = 'desktop')
(select spend_date, platform, count(user_id) as total_users, sum(amount) as total_amount 
 from spending
 where user_id not in (select user_id 
                       from mobile_users)
 group by spend_date)
union all
(select spend_date, platform, count(user_id) as total_users, sum(amount) as total_amount 
 from spending
 where user_id not in (select user_id 
                       from desktop_users)
 group by spend_date)
union all
(select spend_date, 'both' as platform, count(user_id) as total_users, sum(amount) as total_amount 
 from spending
 where user_id in (select user_id from 
                   desktop_users) 
   and user_id in (select user_id 
                   from mobile_users)
 group by spend_date)
 union all 
(
  /* here the code begins */
select spend_date,  platform, count(user_id) as total_users, sum(amount) as total_amount 
 from spending
 where user_id not in (select user_id from 
                   desktop_users) 
   and user_id not in (select user_id 
                   from mobile_users)
 group by spend_date
)
order by spend_date
Related Question