CODEWITHSUNDEEP
mysql
Alias
Alias

Reputation: 721

Group by Month in mysql even when the data is not present

Say I have a table "sales" like below

Customer    Date    Amount
Paul    15/01/2015  2000
Jonathan    15/01/2015  3000
Taylor  15/01/2015  2000
Mark    15/01/2015  3000
Paul    15/02/2015  2000
Jonathan    15/02/2015  3000
Paul    25/02/2015  4000
Jonathan    25/02/2015  5000
Jonathan    15/03/2015  2000
Jonathan    25/04/2015  5000
Taylor  25/04/2015  4000
Mark    25/04/2015  5000
Paul    25/05/2015  4000
Jonathan    25/05/2015  5000
Taylor  25/05/2015  4000
Mark    25/05/2015  5000

And in the report I wan to see it like below - 

Customer    Month   Amount
Paul    201501  2000
Paul    201502  6000
Paul    201503  0
Paul    201504  0
Paul    201505  4000

Please tell me how to do it?

Update

Customer    Month   Amount
Paul    201501  2000
Paul    201502  6000
Paul    201503  0
Paul    201504  0
Paul    201505  4000
Jonathan    201501  3000
Jonathan    201502  8000
Jonathan    201503  2000
Jonathan    201504  5000
Jonathan    201505  5000
Taylor  201501  2000
Taylor  201502  0
Taylor  201503  0
Taylor  201504  4000
Taylor  201505  4000
Mark    201501  3000
Mark    201502  0
Mark    201503  0
Mark    201504  5000
Mark    201505  5000

Also I need to make this view for all the customers. Please tell me how to do that.

Upvotes: 1

Views: 401

Answers (2)

Dylan Su
Dylan Su

Reputation: 6065

Here is a solution, with a demo, using date you provided. Moreover, more data is added for simulation.

drop table sales;

SQL:

-- data
create table sales(Customer varchar(100), `Date` date, Amount int);
insert into sales values
('Paul',    '2015-01-15',  2000),
('Jonathan',    '2015-01-15',  3000),
('Taylor',  '2015-01-15',  2000),
('Mark',    '2015-01-15',  3000),
('Paul',    '2015-02-15',  2000),
('Jonathan',    '2015-02-15',  3000),
('Paul',    '2015-02-25',  4000),
('Jonathan',    '2015-02-25',  5000),
('Jonathan',    '2015-03-15',  2000),
('Jonathan',    '2015-04-15',  5000),
('Taylor',  '2015-04-25',  4000),
('Mark',    '2015-04-25',  5000),
('Paul',    '2015-05-25',  4000),
('Jonathan',    '2015-05-25',  5000),
('Taylor',  '2015-05-25',  4000),
('Mark',    '2015-05-25',  5000);
select * from sales;

-- query wanted
select 
    COALESCE(s.Customer, '') AS Customer, DATE_FORMAT(m2.cont_date, '%Y%m') AS Month, SUM(COALESCE(s.Amount, 0)) AS Amount
from (
    select (select MAX(`Date`) from sales) - interval (year.b * 10 + month.a) MONTH AS cont_date
    from (
        select 0 b union select 1 union select 2 union select 3 union select 4 union 
        select 5 union select 6 union select 7 union select 8 union select 9) AS year
        CROSS JOIN
        (
        select 0 a union select 1 union select 2 union select 3 union select 4 union 
        select 5 union select 6 union select 7 union select 8 union select 9) AS month        
    where (year.b * 10 + month.a) < (select timestampdiff(month, MIN(`Date`), MAX(`Date`)) + 1 from sales) ) m2
    LEFT JOIN sales s ON s.Customer = 'paul' AND DATE_FORMAT(m2.cont_date, '%Y%m') = DATE_FORMAT(s.`Date`, '%Y%m')
GROUP BY Month;

Output:

mysql> select * from sales;
+----------+------------+--------+
| Customer | Date       | Amount |
+----------+------------+--------+
| Paul     | 2015-01-15 |   2000 |
| Jonathan | 2015-01-15 |   3000 |
| Taylor   | 2015-01-15 |   2000 |
| Mark     | 2015-01-15 |   3000 |
| Paul     | 2015-02-15 |   2000 |
| Jonathan | 2015-02-15 |   3000 |
| Paul     | 2015-02-25 |   4000 |
| Jonathan | 2015-02-25 |   5000 |
| Jonathan | 2015-03-15 |   2000 |
| Jonathan | 2015-04-15 |   5000 |
| Taylor   | 2015-04-25 |   4000 |
| Mark     | 2015-04-25 |   5000 |
| Paul     | 2015-05-25 |   4000 |
| Jonathan | 2015-05-25 |   5000 |
| Taylor   | 2015-05-25 |   4000 |
| Mark     | 2015-05-25 |   5000 |
+----------+------------+--------+
16 rows in set (0.00 sec)

mysql>
mysql> -- query wanted
mysql> select
    ->     COALESCE(s.Customer, '') AS Customer, DATE_FORMAT(m2.cont_date, '%Y%m') AS Month, SUM(COALESCE(s.Amount, 0)) AS Amount
    -> from (
    ->     select (select MAX(`Date`) from sales) - interval (year.b * 10 + month.a) MONTH AS cont_date
    ->     from (
    ->         select 0 b union select 1 union select 2 union select 3 union select 4 union
    ->         select 5 union select 6 union select 7 union select 8 union select 9) AS year
    ->         CROSS JOIN
    ->         (
    ->         select 0 a union select 1 union select 2 union select 3 union select 4 union
    ->         select 5 union select 6 union select 7 union select 8 union select 9) AS month
    ->     where (year.b * 10 + month.a) < (select timestampdiff(month, MIN(`Date`), MAX(`Date`)) + 1 from sales) ) m2
    ->     LEFT JOIN sales s ON s.Customer = 'paul' AND DATE_FORMAT(m2.cont_date, '%Y%m') = DATE_FORMAT(s.`Date`, '%Y%m')
    -> GROUP BY Month;
+----------+--------+--------+
| Customer | Month  | Amount |
+----------+--------+--------+
| Paul     | 201501 |   2000 |
| Paul     | 201502 |   6000 |
|          | 201503 |      0 |
|          | 201504 |      0 |
| Paul     | 201505 |   4000 |
+----------+--------+--------+
5 rows in set (0.00 sec)

Upvotes: 1

Morten Rasmussen
Morten Rasmussen

Reputation: 43

You can build a in-line table, with all the months, and then join your sales table on it.

Something like this: 

SELECT s.customer_name as Customer,
CONCAT(months.m, '-', years.y) as Month,
s.amount as Amount
    FROM (
       SELECT '01' AS m UNION ALL SELECT '02' UNION ALL SELECT '03' UNION ALL 
       SELECT '04' UNION ALL SELECT '05' UNION ALL SELECT '06' UNION ALL 
       SELECT '07' UNION ALL SELECT '08' UNION ALL SELECT '09' UNION ALL 
       SELECT '10' UNION ALL SELECT '11' UNION ALL SELECT '12') AS months
    CROSS JOIN (
       SELECT '2015' AS y UNION ALL SELECT '2016') AS years
    LEFT JOIN sales_table AS s
       ON DATE_FORMAT(s.date, '%Y-%c') = CONCAT(years.y, '-', months.m)
    GROUP BY CONCAT(months.m, '-', years.y)

Upvotes: 0

Related Questions