Union with Count OR Join with Sum - MySQL

Question

Hi,

I want to combine three tables - date, lead and click - in a query.

The tables looks like this:

date:

|date|

lead:

id|time|commission

click:

id|time|commission

The table date is just storing dates and is used when getting dates with no click or lead.

So if we have the following data in the tables:

date:

2009-06-01
2009-06-02
2009-06-03

lead:

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

click:

1|2009-06-01|1
2|2009-06-03|2
3|2009-06-03|2
4|2009-06-03|0

I would like to get date, number of click, commission generated by clicks (there are clicks that don't give commission), number of leads, commission generated by leads and total commission. So with the tables above I would like to get:

2009-06-01|1|1|2|700|701|
2009-06-02|0|0|0|0|0
2009-06-03|3|4|1|350|354|

I have tried with the following union:

 SELECT  
 campaign_id, 
 commission_date,  
 SUM( click_commission ) AS click_commission,
 click,
 SUM( lead_commission ) AS lead_commission ,  
 lead,
 SUM( total_commission ) as total_commission
    FROM(
  SELECT  
   click.campaign_id AS campaign_id, 
   DATE( click.time ) AS commission_date, 
      click.commission AS click_commission, 
      (SELECT count(click.id) from click GROUP BY date(click.time)) as click,
      0 as lead_commission,
      0 as lead,
      click.commission AS total_commission
  FROM click
  UNION ALL
  SELECT 
   lead.campaign_id AS campaign_id, 
     DATE( lead.time ) AS commission_date, 
     0 as click_commission,
     0 as click,
   lead.commission AS lead_commission, 
   lead.id as lead,
         lead.commission AS total_commission
  FROM lead
  UNION ALL
  SELECT 
      0 AS campaign_id, 
      date.date AS commission_date, 
   0 AS click_commission, 
   0 as click,
   0 AS lead_commission, 
   0 as lead,
         0 AS total_commission
  FROM date 
 ) AS foo 
 WHERE commission_date BETWEEN '2009-06-01' AND '2009-07-25' 
 GROUP BY  commission_date 
 ORDER BY commission_date LIMIT 0, 10

But this does not work to count both the number of clicks and leads, the code above gives the right amount of clicks bot 0 on all leads. If I move the code around and put the select from the lead table I get the leads right bot 0 on all clicks. I have not been able to find a way to get both of the counts from the query.

So I tried a left-join instead:

SELECT
    date.date as date, 
    count( DISTINCT click.id ) AS clicks, 
    sum(click.commission) AS click_commission, 
    count( lead.id ) AS leads, 
    sum(lead.commission) AS lead_commission
FROM date
LEFT JOIN click ON ( date.date = date( click.time ) )
LEFT JOIN lead ON ( date.date = date( lead.time ) )
GROUP BY date.date
LIMIT 0 , 30

The problem with this query is if there are more than one clicks or leads on a date it will return the expected value * 2. So on 2009-06-01 it will return 1400 instead on the expected 700 for lead commission.

So in the UNION I have problems with the count and in the left join it is the SUM that is not working.

I would really like to stick to the UNION if possible, but I haven't found a way to get both counts from it.

(This is a follow up to this earlier question, but since I didn't ask for the count in that I posted a new question.)

Answer 1

SELECT  date,
        COALESCE(lcomm, 0), COALESCE(lcnt, 0),
        COALESCE(ccomm, 0), COALESCE(ccnt, 0),
        COALESCE(ccomm, 0) + COALESCE(lcomm, 0),
        COALESCE(ccnt, 0) + COALESCE(lcnt, 0)
LEFT JOIN
        (
        SELECT  date, SUM(commission) AS lcomm, COUNT(*) AS lcnt
        FROM    leads
        GROUP BY
                date
        ) l
ON      l.date = d.date
LEFT JOIN
        (
        SELECT  date, SUM(commission) AS ccomm, COUNT(*) AS ccnt
        FROM    clicks
        GROUP BY
                date
        ) с
ON      c.date = d.date
FROM    date d

Answer 2

The code that I used, built from the suggestion from Quassnoi:

SELECT  date,
        COALESCE(ccomm, 0) AS click_commission, COALESCE(ccnt, 0) AS click_count,
        COALESCE(lcomm, 0) AS lead_commision, COALESCE(lcnt, 0) AS lead_count,
        COALESCE(ccomm, 0) + COALESCE(lcomm, 0) as total_commission
FROM    date d
LEFT JOIN
        (
        SELECT  DATE(time) AS lead_date, SUM(commission) AS lcomm, COUNT(*) AS lcnt
        FROM    lead
        GROUP BY
                lead_date
        ) l
ON     lead_date = date
LEFT JOIN
        (
        SELECT  DATE(time) AS click_date, SUM(commission) AS ccomm, COUNT(*) AS ccnt
        FROM    click
        GROUP BY
                click_date
        ) с
ON      click_date =  date