Hi,
I am trying to create an aggregated query using sub queries across multiple tables.
I can work out how to get my 'submission' or 'completions' , but am struggling to join them together.
The above works for just the subs (sub_val = 54), but as soon as I try to include the comps, I get completely incorrect results...
with this sub_val = 864?
something is being multiplied here and I can't work out how to group and sub query correctly to get the correct aggregates?
I have a working query using sub queries in the select statement, but it runs slower than I was hoping for.
All help appreciated.
1DMF
"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."
"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Dance Music Downloads
I am trying to create an aggregated query using sub queries across multiple tables.
I can work out how to get my 'submission' or 'completions' , but am struggling to join them together.
Code:
SELECT DISTINCT m.CompanyName, LEFT(m.ID, 8) AS ARNO, SUM(subs.sub_cnt) AS sub_cnt, SUM(subs.sub_val) AS sub_val
FROM Members AS m LEFT OUTER JOIN
(SELECT LEFT(Adv_MemNo, 8) AS adv_memno, COUNT(1) AS sub_cnt, SUM(Commission) AS sub_val
FROM Business_Register AS b1
WHERE (Status = 'SUB') AND (Ins_Lnk IS NULL)
GROUP BY Adv_MemNo) AS subs ON subs.adv_memno = LEFT(m.ID, 8)
WHERE (m.ID LIKE 'n%') AND (m.Role <> 'locked') AND (m.CompanyName IS NOT NULL) AND (m.CompanyName LIKE 'brag%')
GROUP BY m.CompanyName, m.ID
ORDER BY m.CompanyName
The above works for just the subs (sub_val = 54), but as soon as I try to include the comps, I get completely incorrect results...
Code:
SELECT DISTINCT
m.CompanyName, LEFT(m.ID, 8) AS ARNO, SUM(subs.sub_cnt) AS sub_cnt, SUM(subs.sub_val) AS sub_val, SUM(comps.comp_cnt) AS comp_cnt,
SUM(comps.comp_val) AS comp_val
FROM Members AS m LEFT OUTER JOIN
(SELECT LEFT(Adv_MemNo, 8) AS adv_memno, COUNT(1) AS sub_cnt, SUM(Commission) AS sub_val
FROM Business_Register AS b1
WHERE (Status = 'SUB') AND (Ins_Lnk IS NULL)
GROUP BY Adv_MemNo) AS subs ON subs.adv_memno = LEFT(m.ID, 8) LEFT OUTER JOIN
(SELECT LEFT(Adv_MemNo, 8) AS adv_memno, COUNT(1) AS comp_cnt, SUM(Commission) AS comp_val
FROM Business_Register AS b2
WHERE (Status = 'COMP') AND (Ins_Lnk IS NULL)
GROUP BY Adv_MemNo) AS comps ON comps.adv_memno = LEFT(m.ID, 8)
WHERE (m.ID LIKE 'n%') AND (m.Role <> 'locked') AND (m.CompanyName IS NOT NULL) AND (m.CompanyName LIKE 'brag%')
GROUP BY m.CompanyName, m.ID
ORDER BY m.CompanyName
with this sub_val = 864?
something is being multiplied here and I can't work out how to group and sub query correctly to get the correct aggregates?
I have a working query using sub queries in the select statement, but it runs slower than I was hoping for.
Code:
SELECT DISTINCT m.CompanyName, LEFT(m.ID, 8) AS ARNO,
(SELECT COUNT(1) AS sub_cnt
FROM Business_Register
WHERE (Adv_MemNo LIKE LEFT(m.ID, 8) + '%') AND (Status = 'SUB') AND (Ins_Lnk IS NULL)) AS sub_cnt,
(SELECT COUNT(1) AS comp_cnt
FROM Business_Register AS Business_Register_3
WHERE (Adv_MemNo LIKE LEFT(m.ID, 8) + '%') AND (Status = 'COMP') AND (Ins_Lnk IS NULL)) AS comp_cnt,
(SELECT SUM(Commission) AS sub_val
FROM Business_Register AS Business_Register_2
WHERE (Adv_MemNo LIKE LEFT(m.ID, 8) + '%') AND (Status = 'SUB') AND (Ins_Lnk IS NULL)) AS sub_val,
(SELECT SUM(Commission) AS comp_val
FROM Business_Register AS Business_Register_1
WHERE (Adv_MemNo LIKE LEFT(m.ID, 8) + '%') AND (Status = 'COMP') AND (Ins_Lnk IS NULL)) AS comp_val
FROM Members AS m LEFT OUTER JOIN
Business_Register AS b ON m.ID = b.Adv_MemNo
WHERE (m.ID LIKE 'n%') AND (m.Role <> 'locked') AND (m.CompanyName IS NOT NULL) AND (m.CompanyName LIKE 'brag%')
All help appreciated.
1DMF
"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."
"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Dance Music Downloads