Aggregate data from two tables with different field names

Question

I have two tables that each have date and amount fields. This is for a donation system so one is pledges and the other is donations. Here are the structure of relevant tables and fields:

pledges

• pledge_date
• amount_pledged

donations

• processed_date
• amount_donated

I would like to end up with results that sum pledges and donations by month. Resulting in:

Year    Month    Pledged    Donated
2015    01       120        80
2015    02       90         100
2015    05       0          10
2015    06       120        0

Obviously it's easy enough to get the data summed and grouped from the tables individually, but is there a way to get it all in one result set? Here is the sample SQL to get the data just from the pledges table for example:

SELECT year(pledge_date) as year, month(pledge_date) as month, sum(amount_pledged) as pledged 
FROM pledges
GROUP by year(pledge_date), month(pledge_date)
ORDER by pledge_date ASC

I am trying to accomplish this in MySQL.

Thanks to anyone for your help or clues on how to structure this!

Answer 1

SELECT year, month, sum(pledged) AS Pledged, sum(donated) AS DONATED FROM
(
SELECT year(pledge_date) as year, month(pledge_date) as month, amount_pledged  as pledged, 0 as donated FROM pledges
UNION
SELECT year(processed_date) as year, month(processed_date) as month, 0 as pledged, amount_donated as donated FROM **donations**
)x 
GROUP BY year, month 
ORDER BY year, month

Answer 2

UNION is your answer

SELECT YEAR(p.pledge_date) AS year, MONTH(p.pledge_date) AS month, SUM(p.amount_pledged) AS pledged 
FROM pledges p
UNION 
SELECT YEAR(d.processed_date) AS year, MONTH(d.processed_date) AS month, SUM(d.amount_donated) AS pledged 
FROM donations d
#no need for group
ORDER by year ASC ,month ASC

Answer 3

Jehad, your answer got me close but I was missing some values from the donation table for some reason. Anyhow, this seems to give the correct results although I'm not sure it's the most efficient. I landed on this after learning that MySQL doesn't support full outer joins.

SELECT a.pledged, a.year AS year1, a.month AS month1, b.year AS year2, b.month AS month2, b.donated FROM 
( 
SELECT YEAR(pledge_date) AS YEAR, MONTH(pledge_date) AS MONTH, SUM(donation_amt_pledged) AS pledged FROM pledges 
    GROUP BY YEAR(pledge_date), MONTH(pledge_date) 
) a 
RIGHT JOIN 
( 
SELECT YEAR(d.processed_date) AS YEAR, MONTH(d.processed_date) AS MONTH, SUM(d.amount_donated) AS donated FROM donations d
    LEFT JOIN pledges p ON p.id = d.pledge_id 
    GROUP BY YEAR(d.processed_date), MONTH(d.processed_date) 
) b 
ON a.YEAR = b.YEAR AND a.MONTH = b.MONTH 
UNION 
SELECT a.pledged, a.year AS year1, a.month AS month1, b.year AS year2, b.month AS month2, b.donated FROM 
( 
SELECT YEAR(pledge_date) AS YEAR, MONTH(pledge_date) AS MONTH, SUM(donation_amt_pledged) AS pledged FROM pledges 
GROUP BY YEAR(pledge_date), MONTH(pledge_date) 
) a 
LEFT JOIN 
( 
SELECT YEAR(d.processed_date) AS YEAR, MONTH(d.processed_date) AS MONTH, SUM(d.amount_donated) AS donated FROM donations d
    LEFT JOIN pledges p ON p.id = d.pledge_id 
    GROUP BY YEAR(d.processed_date), MONTH(d.processed_date) 
) b 
ON a.YEAR = b.YEAR AND a.MONTH = b.MONTH 

Answer 4

Have you tried joining by YEAR and MONTH?

Try this:

SELECT don.donyear AS "Year", don.donmonth AS "Month", ple.plesum "Pledged", don.donsum "Donated" 
FROM 
( 
SELECT YEAR(processed_date) AS "donyear", MONTH(processed_date) AS "donmonth", SUM(amount_donated) "donsum" 
FROM donations 
GROUP BY YEAR(processed_date), MONTH(processed_date) 
) don 
INNER JOIN 
( 
SELECT YEAR(pledge_date) AS "pleyear", MONTH(pledge_date) AS "plemonth", SUM(amount_pledged) AS "plesum" 
FROM pledges 
GROUP BY YEAR(pledge_date), MONTH(pledge_date) 
) ple 
ON don.donyear = ple.pleyear AND don.donmonth = ple.plemonth
ORDER BY don.donyear, don.donmonth ASC;