I have 2 tables:
table1
date | client | total
2015-01-01 | john | 85.00
2015-02-01 | alfred | 35.00
2015-03-01 | georgy | 125.00
table2
date | client | total
2015-01-01 | Wendy | 25.00
2015-02-01 | Monique | 19.00
2015-03-01 | Jack | 14.00
When I need to join these tables I do it with this query:
SELECT * FROM (SELECT date, client, total from table1
UNION ALL SELECT date, client, total from table2) a where date BETWEEN '2015-01-01' AND '2015-06-11';
And I get this:
date | client | total
2015-01-01 | john | 85.00
2015-01-01 | Wendy | 25.00
2015-02-01 | alfred | 35.00
2015-02-01 | Monique | 19.00
2015-03-01 | georgy | 125.00
2015-03-01 | Jack | 14.00
So far so good but now I want to group these values by year, month and adding the totals so that it stays like this:
year | month | total
2015 | 01 | 110.00
2015 | 02 | 54.00
2015 | 03 | 139.00
How can I modify the query?
If
date
it is indeed a date, we can use itMONTH()
to obtain the month ofdate
and group by it. For the year we do the same usingYEAR(date)