Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

correlated sub querries with aggregate functions using mutiple joins 1

Status
Not open for further replies.
1DMF

1DMF

Programmer
Jan 18, 2005
8,795
GB
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.

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
 
Sort by date Sort by votes
I've had a brief look and the only thing that seems to leap out at me is that LEFT OUTER JOIN you have between your 2 sub and comp derived tables, I think that this bit...

GROUP BY Adv_MemNo) AS subs ON subs.adv_memno = LEFT(m.ID, 8) LEFT OUTER JOIN

should read

GROUP BY Adv_MemNo) AS subs ON subs.adv_memno = LEFT(m.ID, 8) UNION ALL

I haven't tested it.
 
Upvote 0 Downvote
Upvote 0 Downvote
I thought I'd try to join the two derived tables together first but that won't work either?

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(comp_val) AS comp_val

FROM         

Members AS m LEFT OUTER JOIN 

(

                          (SELECT    adv_memno, COUNT(1) AS sub_cnt, SUM(Commission) AS sub_val
                            FROM          Business_Register                            
                           WHERE      (Status = 'SUB') AND (Ins_Lnk IS NULL)
                            GROUP BY Adv_MemNo) subs

join

                          (SELECT     adv_memno, COUNT(1) AS comp_cnt, SUM(Commission) AS comp_val
                            FROM          Business_Register
                            WHERE      (Status = 'COMP') AND (Ins_Lnk IS NULL)
                            GROUP BY Adv_MemNo) comps

)
 
                            as b
                           ON b.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

"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
 
Upvote 0 Downvote
ok, I seem to be getting somewhere with
Code: 
SELECT DISTINCT 
                      m.CompanyName, LEFT(m.ID, 8) AS ARNO, SUM(b.sub_val) AS sub_val, SUM(b.sub_cnt) AS sub_cnt, SUM(b.comp_val) AS comp_val, SUM(b.comp_cnt) 
                      AS comp_cnt
FROM         Members AS m LEFT OUTER JOIN
                          (SELECT     subs.Adv_MemNo, 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          (SELECT     Adv_MemNo, COUNT(1) AS sub_cnt, SUM(Commission) AS sub_val
                                                    FROM          Business_Register
                                                    WHERE      (Status = 'SUB') AND (Ins_Lnk IS NULL)
                                                    GROUP BY Adv_MemNo) AS subs LEFT OUTER JOIN
                                                       (SELECT     Adv_MemNo, COUNT(1) AS comp_cnt, SUM(Commission) AS comp_val
                                                         FROM          Business_Register AS Business_Register_1
                                                         WHERE      (Status = 'COMP') AND (Ins_Lnk IS NULL)
                                                         GROUP BY Adv_MemNo) AS comps ON subs.Adv_MemNo = comps.Adv_MemNo
                            GROUP BY subs.Adv_MemNo) AS b ON LEFT(b.Adv_MemNo, 8) = 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

so joining the two derived table as a derived table. only I don't get the same results as the version using correlated sub query in the select clause?

"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
 
Upvote 0 Downvote
Yes, it won't work, these two datasets are mutually exclusive, they don't need to be joined, they need to be combined.
 
Upvote 0 Downvote
Sorry, I've had a long day and not thinking straight, yes, you are on the right track...I got confused by th layout of thw query.

This is also untested but looks like what you've already got. Well done :)

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 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 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
 
Upvote 0 Downvote
yes, thanks, with your guidance , I have cracked it :)

Code: 
SELECT DISTINCT 
                      m.CompanyName, LEFT(m.ID, 8) AS ARNO, SUM(b.sub_val) AS sub_val, SUM(b.sub_cnt) AS sub_cnt, SUM(b.comp_val) AS comp_val, SUM(b.comp_cnt) 
                      AS comp_cnt
FROM         Members AS m LEFT OUTER JOIN
                          ((SELECT     Adv_MemNo, COUNT(1) AS sub_cnt, SUM(Commission) AS sub_val, 0 AS comp_val, 0 AS comp_cnt
                              FROM         Business_Register
                              WHERE     (Status = 'SUB') AND (Ins_Lnk IS NULL)
                              GROUP BY Adv_MemNo)
                      UNION ALL
                      (SELECT     Adv_MemNo,  0 AS sub_cntl, 0 AS sub_val, SUM(Commission) AS comp_val,COUNT(1) AS comp_cnt
                       FROM         Business_Register
                       WHERE     (Status = 'COMP') AND (Ins_Lnk IS NULL)
                       GROUP BY Adv_MemNo)) AS b ON LEFT(b.Adv_MemNo, 8) = 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

I just had to get the columns in the right order and include zero'd cols for the union to combine correctly so I can then do the outer join!

Was a bit mind bending but I got there in the end, thanks for the input.

"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
 
Upvote 0 Downvote
Right. Ignore my posts, I shouldn't have posted anything at all with my brain currently at half mast. Normally I try everything out _before_ I post a reply but I thought I could get away with winging it and all I've done is confuse myself. I'm off to bed.
 
Upvote 0 Downvote
Stout fella, well done. Anyone who managed to get the correct result with my meadering input deserves a medal :) Good night.
 
Upvote 0 Downvote
Upvote 0 Downvote
This may (or may not) run faster as it eliminates UNION and DISTINCT

Code: 
WITH m As (
SELECT LEFT(ID, 8) As ARNO, CompanyName
  FROM Members
 WHERE ID LIKE 'n%' AND Role <> 'locked' AND CompanyName LIKE 'brag%'
),

WITH b As (
SELECT LEFT(Adv_Memo, 8) As ARNO, Status, COUNT(1) As cnt, SUM(Commission) As val
  FROM Business_Register
 WHERE Ins_Lnk IS NULL AND Status IN ('COMP','SUB')
   AND LEFT(Adv_Memo, 8) IN (SELECT ARNO FROM m)
 GROUP BY LEFT(Adv_Memo, 8), Status
)

SELECT m.ARNO, m.CompanyName,
	   SUM(CASE WHEN b.Status = 'SUB' THEN b.val ELSE 0.00 END) As sub_val,
	   SUM(CASE WHEN b.Status = 'SUB' THEN b.cnt ELSE 0 END) As sub_cnt,
	   SUM(CASE WHEN b.Status = 'COMP' THEN b.val ELSE 0.00 END) As comp_val,
	   SUM(CASE WHEN b.Status = 'COMP' THEN b.cnt ELSE 0 END) As comp_cnt
  FROM m
  LEFT
  JOIN b
    ON m.ARNO = b.ARNO
 GROUP BY m.ARNO, m.CompanyName
 
Upvote 0 Downvote
Thanks, though the solution I posted runs in under a second, so I have resolved my initial 'time' problem.

I also can't run a compound query with my current application CRUD, though I could create an SP and use that instead :)

Though having tested in SQL EM , it is erroring on the second 'WITH'
Incorrect syntax near the keyword 'WITH'.
Click to expand...

"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
 
Upvote 0 Downvote
Yeah, since I don't have your tables I was flying blind without Intellisense. The second WITH should be removed.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

gussy81
  • Locked
  • Question
Decimal Datatype with negative value
Replies
5
Views
678
Chris Miller
Chris Miller
elly00
  • Locked
  • Question
create sql view using batch file
Replies
5
Views
246
Mike Lewis
Mike Lewis
Auguy
  • Locked
  • Question
Using UDF In Select, Bad Idea? 2
Replies
3
Views
159
Auguy
Auguy
Cpreston
  • Locked
  • Question
Update with a month name 1
Replies
9
Views
389
gmmastros
gmmastros
TheBugSlayer
  • Locked
  • Question
Transaction with SELECT and UPDATE with XLOCK causing deadlocks 2
Replies
10
Views
368
gmmastros
gmmastros

Part and Inventory Search

Sponsor

Back
Top