0

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!

1
  • JOIN. But you need some column(s) that tie the two tables together.
    – Rick James
    Apr 3, 2015 at 17:45

4 Answers 4

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
1
  • 1
    This is the one, perfect once the second SELECT is FROM donations (i submitted a fix), cheers Keriaki !
    – Dr. Tyrell
    Apr 8, 2015 at 5:19
0

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
0

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 
0

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.