Jump to content

Need Help to Join to SQL Queries


n1concepts
Go to solution Solved by n1concepts,

Recommended Posts

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 by n1concepts
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.