Need Help to Join to SQL Queries

[n1concepts]

Hi,

 

I have two MySQL queries - each pulls the exact information I need when loaded on its own via CLI.

However, I need to join the two - insert one as a subquery - so I can then use the 'COUNT(cl.reqid)' results for each grouped set of data results parsed from that query into the later in order to complete the 'epc' calculation.

 

Below are the two quereis - I know the "GROUP BY" clauses  creating the multiple rows but only way I could segment the data (per date and a.affid and s.subid) to show individual sets of data to get correct total count of cl.reqid.

 

I need help combining the two queries so I can then calculate the epc (that formula will be: (c.payout * COUNT(l.reqid) / COUNT(cl.reqid) as epc) - which will be added to the combined query.

BELOW is the main query - it runs without issues (as is)

SELECT DATE(l.time1) as date,a.aname,s.subid,
COUNT(l.reqid),c.camp_name,c.campid FROM leads l 
LEFT JOIN affiliates AS a ON l.affid = a.affid 
LEFT JOIN subaffiliates AS s ON l.subid = s.subaffid 
LEFT JOIN campaign AS c ON l.campid = c.campid 
WHERE (a.affid = l.affid) 
AND (l.subid = s.subaffid) 
AND (l.campid = c.campid)  
GROUP BY DATE(l.time1),a.aname,s.subid 
ORDER BY a.aname,DATE(l.time1) ASC;

 

Next (below) is the 2nd query to which I need to include into the above query - I assume as a subquery and would prefer to have the 'COUNT(cl.reqid)' value display right after ,'s.subid'.

Also, I need to use both 'COUNT(l.reqid)' and 'COUNT(cl.reqid)' to perform the 'epc' calculation.

 

SELECT a.aname,s.subid,DATE(cl.cdate),COUNT(cl.reqid),c.camp_name,c.campid from clicks AS cl 
LEFT JOIN affiliates as a ON a.affid = cl.affid_sid 
LEFT JOIN subaffiliates as s ON s.subaffid = cl.subid 
LEFT JOIN campaign as c ON c.campid = cl.camp_sid 
WHERE (a.affid = cl.affid_sid)
AND (s.subid != '')  
AND (s.subaffid = cl.subid) 
AND (c.campid = cl.camp_sid)
GROUP BY DATE(cdate),a.aname,s.subid 
ORDER BY a.aname,DATE(cdate);

 

 

I tried this (see below) just to see what I would get - the 'multiple rows' error due to repeating date from DATE(cl.date) I know but now sure how to handle that...

BTW: that date (cl.cdate) does not match (l.time1) - two separate entried so can't use those to pair records. Anyway, this is what I came up with thus far to which i need help working into correct structure:

 

SELECT DATE(l.time1) as date,a.aname,s.subid,

(SELECT a.aname,s.subid,DATE(cl.cdate),COUNT(cl.reqid),c.camp_name,c.campid,c.payout from clicks AS cl 
LEFT JOIN affiliates as a ON a.affid = cl.affid_sid 
LEFT JOIN subaffiliates as s ON s.subaffid = cl.subid 
LEFT JOIN campaign as c ON c.campid = cl.camp_sid 
WHERE (a.affid = cl.affid_sid)
AND (s.subid != '')  
AND (s.subaffid = cl.subid) 
AND (c.campid = cl.camp_sid)
GROUP BY DATE(cdate),a.aname,s.subid 
ORDER BY a.aname,DATE(cdate)),

COUNT(l.reqid),c.camp_name,c.campid 
(c.payout * COUNT(l.reqid) / COUNT(cl.reqid) as epc) 
FROM leads l 
LEFT JOIN affiliates AS a ON l.affid = a.affid 
LEFT JOIN subaffiliates AS s ON l.subid = s.subaffid 
LEFT JOIN campaign AS c ON l.campid = c.campid 
WHERE (a.affid = l.affid) 
AND (l.subid = s.subaffid) 
AND (l.campid = c.campid)  
GROUP BY DATE(l.time1),a.aname,s.subid 
ORDER BY a.aname,DATE(l.time1) ASC;

 

Really appreciate if someone can explain how I can combine the 1st two to form the correct subquery you see in last post (I know that one is wrong due to "GROUP BY" clause - just not sure how to address.

Thanks for help!

[n1concepts]

Solution:

1. create two separate views, then

2, call those two views in 3rd (extracting the data I need based on date column which will pull correct set of data from both vtables.