Need to combine totals of two table columns

[n1concepts]

I performed a UNION on two tables - 'leads' and 'ext_leads' to group several columns which are identical but one in particular which is an aggregate.

Note: on both, I need to get total count of that one column and on both I named the alias total_leads - see UNION query below:

SELECT DATE(l.time1) as date,a.affid,a.aname,
COUNT(l.reqid) as total_leads FROM leads l 
LEFT JOIN affiliates AS a ON l.affid = a.affid 
LEFT JOIN campaign AS c ON l.campid = c.campid 
WHERE (a.affid = l.affid) 
GROUP BY DATE(l.time1),a.aname 
UNION ALL 
SELECT DATE(x.extstamp) as date,a.affid,a.aname,
COUNT(x.reqid) as total_leads FROM ext_leads x 
LEFT JOIN clicks AS cl ON cl.reqid = x.reqid
LEFT JOIN affiliates AS a ON a.affid = cl.affid_sid    
WHERE (cl.reqid = x.reqid)    
GROUP BY DATE(x.extstamp),a.aname 
ORDER BY date;

And the results is as follow which pulls one row - per 'aname' from each of the two tables - 'leads' and 'ext_leads'.

However, I need to take this one step further and combine the totals pulled (per aname) from 'leads' with those for same 'aname' from 'ext_leads'

+------------+-------+---------------------------+-------------+
| date       | id    | aname                     | total_leads |
+------------+-------+---------------------------+-------------+
| 2013-05-27 |   121 | ABC, LLC                  |         145 |
| 2013-05-27 |   121 | ABC, LLC                  |          33 |
| 2013-05-29 |   122 | Company x                 |          67 |
| 2013-05-29 |   122 | Company x                 |           2 |
+------------+-------+---------------------------+-------------+
2 rows in set

=======

What I need is to combine the total leads from the 'leads' & 'ext_leads' tables so that:
ABC, LLC total for 5/27 show 178 and Company x totals is 69.

I tried SUM() function and calling the (above) query as a view but getting cross-joins so need some advise on how to merge the two totals and group by 'aname' column sorting by date - descending to get desired results.

 

Any help to form final query to acheive goal truly appreciated - thx!
MYSQL version: 5.3

[Barand]

try

SELECT date, aname, SUM(total_leads) as total
FROM
    (
    SELECT DATE(l.time1) as date,a.affid,a.aname,
        COUNT(l.reqid) as total_leads FROM leads l 
        LEFT JOIN affiliates AS a ON l.affid = a.affid 
        LEFT JOIN campaign AS c ON l.campid = c.campid 
        WHERE (a.affid = l.affid) 
        GROUP BY DATE(l.time1),a.aname 
    UNION ALL 
    SELECT DATE(x.extstamp) as date,a.affid,a.aname,
        COUNT(x.reqid) as total_leads FROM ext_leads x 
        LEFT JOIN clicks AS cl ON cl.reqid = x.reqid
        LEFT JOIN affiliates AS a ON a.affid = cl.affid_sid    
        WHERE (cl.reqid = x.reqid)    
        GROUP BY DATE(x.extstamp),a.aname
    ) as sub
ORDER BY date, aname

[n1concepts]

Worked like a charm...

Appreciate your help on this - Thanks!

[Barand]

That final ORDER BY should be GROUP BY. Guess you spotted that.