n1concepts Posted April 4, 2013 Share Posted April 4, 2013 (edited) 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! Edited April 4, 2013 by n1concepts Quote Link to comment Share on other sites More sharing options...
Solution n1concepts Posted April 4, 2013 Author Solution Share Posted April 4, 2013 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.