boingboing boingboing - 3 months ago 10
MySQL Question

Group sum from two tables according to date in MySQL

I have two different tables, lead and click. I would like to query MySQL to get the sum of the commissions generated by clicks and leads ordered by date.

Lead:

id|date |commission
1|2009-06-01|400
2|2009-06-01|300
3|2009-06-03|350


Click:

id|date |commission
1|2009-06-01|1
2|2009-06-03|2
3|2009-06-03|1


I would like to create a query that gives me (and if possible also gets date where no lead or click has been generated):

date |commission click|commission lead|total commission
2009-06-01| 1| 700| 701
2009-06-02| 0| 0| 0
2009-06-02| 3| 350| 353


(The date is actually datetime in the real database.)

I guess I have to combine:

SELECT count(*) as number_clicks, sum(click.commission) as sum_clicks,
date(click.time) as click_date from click group by click_date order by click_date


With:

SELECT count(*)as number_leads, sum(lead.commission) as sum_leads,
date(lead.time) as lead_date from lead group by lead_date order by lead_date


But I can not get them to work together.

lc. lc.
Answer

This doesn't get dates with zeroes, for that you'll either need a dates table or a stored procedure to loop through dates. One way to do it is a subselect from a union query (untested):

SELECT commission_date, SUM(click_commission), SUM(lead_commission), SUM(total_commission)
FROM (SELECT DATE(click.time) AS commission_date, click.commission AS click_commission,
             0 AS lead_commission, click.commission AS total_commission
      FROM click
      UNION ALL
      SELECT DATE(lead.time) AS commission_date, 0 AS click_commission,
             lead.commission AS lead_commission, lead.commission AS total_commission
      FROM lead) AS foo
GROUP BY commission_date
ORDER BY commission_date