Cross Apply not summing correctly

• tshad

  SSCertifiable

  Points: 5905

  More actions

  June 8, 2018 at 8:06 pm

  #369520

  I have a group of records that I can sort of get working  but the inner cross apply is doubling the amounts.  I assume that is because it is doing it once for each record in the outer select.

  I also want to get the inner count in the outer select but can't figure out how to get it out as it would need to be part of the Group By clause which would not work.

  Here is my schema and code:
  
Declare @test-2 TABLE 
(
 FullName varchar(20),
 AdjType int,
 Adjustment int
)

  INSERT @test-2 values('Tom Jones', 2, 10)
  INSERT @test-2 values('Tom Jones', 2, 15)
  INSERT @test-2 values('Tom Jones', 3, 100)
  INSERT @test-2 values('Tom Jones', 3, 150)
  INSERT @test-2 values('Ron Olsen', 2, 1000)
  INSERT @test-2 values('Ron Olsen', 2, 1500)
  INSERT @test-2 values('Ron Olsen', 3, 10000)
  INSERT @test-2 values('Ron Olsen', 3, 15000)

  
  SELECT baDental.FullName, COUNT(*) AS AdjustmentDentalCount, SUM(baDental.Adjustment) AS AdjustmentDental, SUM(baVision.Adjustment) AS AdjustmentVision
  FROM @test-2 baDental
  CROSS APPLY
  (
   SELECT FullName, COUNT(*) AS AdjustmentVisionCount, SUM(Adjustment) AS Adjustment
   FROM @test-2 ba2
   WHERE ba2.AdjType = 3 AND    -- Vision
      baDental.FullName = ba2.FullName
   GROUP BY FullName
  ) baVision
  WHERE baDental.AdjType = 2   -- Dental
  GROUP BY baDental.FullName

  The results are:

  

  Thanks

• saravanatn

  SSCarpal Tunnel

  Points: 4533

  More actions

  June 8, 2018 at 10:51 pm

  #1993465

  You did the 1st part right by posting the sample data. Now can post us the desired results. Then what version of  sql server are you using? because higher version of sql server doesn't requires sum with group by function.. Rather we can usesum with partition by

  Saravanan

• Eirikur Eiriksson

  SSC Guru

  Points: 182879

  More actions

  June 8, 2018 at 11:22 pm

  #1993466

  You can simplify this query, no need to use cross apply or a CTE.
  😎

  
USE TEEST;
GO
SET NOCOUNT ON

  Declare @test-2 TABLE
(
FullName varchar(20),
AdjType int,
Adjustment int
)
INSERT @test-2 values('Tom Jones', 2, 10)
INSERT @test-2 values('Tom Jones', 2, 15)
INSERT @test-2 values('Tom Jones', 3, 100)
INSERT @test-2 values('Tom Jones', 3, 150)
INSERT @test-2 values('Ron Olsen', 2, 1000)
INSERT @test-2 values('Ron Olsen', 2, 1500)
INSERT @test-2 values('Ron Olsen', 3, 10000)
INSERT @test-2 values('Ron Olsen', 3, 15000)

  SELECT
 T.FullName
,SUM(CASE WHEN T.AdjType = 2 THEN T.Adjustment END) AS AdjustmentDental
,SUM(CASE WHEN T.AdjType = 3 THEN T.Adjustment END) AS AdjustmentVision
,SUM(CASE WHEN T.AdjType = 2 THEN 1 END) AS AdjustmentDentalCount
,SUM(CASE WHEN T.AdjType = 3 THEN 1 END) AS AdjustmentVisionCount
FROM  @test-2 T
GROUP BY T.FullName
ORDER BY T.FullName;

  Output
  
FullName  AdjustmentDental AdjustmentVision AdjustmentDentalCount AdjustmentVisionCount
----------- ---------------- ---------------- --------------------- ---------------------
Ron Olsen 2500     25000    2       2
Tom Jones 25     250     2       2


• saravanatn

  SSCarpal Tunnel

  Points: 4533

  More actions

  June 8, 2018 at 11:42 pm

  #1993467

  Eirikur Eiriksson - Friday, June 8, 2018 11:22 PM

  You can simplify this query, no need to use cross apply or a CTE.
  😎

  
USE TEEST;
GO
SET NOCOUNT ON

  Declare @test-2 TABLE
(
FullName varchar(20),
AdjType int,
Adjustment int
)
INSERT @test-2 values('Tom Jones', 2, 10)
INSERT @test-2 values('Tom Jones', 2, 15)
INSERT @test-2 values('Tom Jones', 3, 100)
INSERT @test-2 values('Tom Jones', 3, 150)
INSERT @test-2 values('Ron Olsen', 2, 1000)
INSERT @test-2 values('Ron Olsen', 2, 1500)
INSERT @test-2 values('Ron Olsen', 3, 10000)
INSERT @test-2 values('Ron Olsen', 3, 15000)

  SELECT
 T.FullName
,SUM(CASE WHEN T.AdjType = 2 THEN T.Adjustment END) AS AdjustmentDental
,SUM(CASE WHEN T.AdjType = 3 THEN T.Adjustment END) AS AdjustmentVision
,SUM(CASE WHEN T.AdjType = 2 THEN 1 END) AS AdjustmentDentalCount
,SUM(CASE WHEN T.AdjType = 3 THEN 1 END) AS AdjustmentVisionCount
FROM  @test-2 T
GROUP BY T.FullName
ORDER BY T.FullName;

  Output
  
FullName  AdjustmentDental AdjustmentVision AdjustmentDentalCount AdjustmentVisionCount
----------- ---------------- ---------------- --------------------- ---------------------
Ron Olsen 2500     25000    2       2
Tom Jones 25     250     2       2


  Sorry didn't see the desired results on OP post.  Great solution😊😊😊

  Saravanan

• tshad

  SSCertifiable

  Points: 5905

  More actions

  June 11, 2018 at 1:32 am

  #1993504

  I was able to use this in the actual select statement and it worked perfectly.

  Thanks.

• Eirikur Eiriksson

  SSC Guru

  Points: 182879

  More actions

  June 11, 2018 at 2:57 am

  #1993510

  tshad - Monday, June 11, 2018 1:32 AM

  I was able to use this in the actual select statement and it worked perfectly.

  Thanks.

  You are very welcome.
  😎

  Question, do you know how it works?

• saravanatn

  SSCarpal Tunnel

  Points: 4533

  More actions

  June 11, 2018 at 4:00 am

  #1993514

  saravanatn - Friday, June 8, 2018 11:42 PM

  Eirikur Eiriksson - Friday, June 8, 2018 11:22 PM

  You can simplify this query, no need to use cross apply or a CTE.
  😎

  
USE TEEST;
GO
SET NOCOUNT ON

  Declare @test-2 TABLE
(
FullName varchar(20),
AdjType int,
Adjustment int
)
INSERT @test-2 values('Tom Jones', 2, 10)
INSERT @test-2 values('Tom Jones', 2, 15)
INSERT @test-2 values('Tom Jones', 3, 100)
INSERT @test-2 values('Tom Jones', 3, 150)
INSERT @test-2 values('Ron Olsen', 2, 1000)
INSERT @test-2 values('Ron Olsen', 2, 1500)
INSERT @test-2 values('Ron Olsen', 3, 10000)
INSERT @test-2 values('Ron Olsen', 3, 15000)

  SELECT
 T.FullName
,SUM(CASE WHEN T.AdjType = 2 THEN T.Adjustment END) AS AdjustmentDental
,SUM(CASE WHEN T.AdjType = 3 THEN T.Adjustment END) AS AdjustmentVision
,SUM(CASE WHEN T.AdjType = 2 THEN 1 END) AS AdjustmentDentalCount
,SUM(CASE WHEN T.AdjType = 3 THEN 1 END) AS AdjustmentVisionCount
FROM  @test-2 T
GROUP BY T.FullName
ORDER BY T.FullName;

  Output
  
FullName  AdjustmentDental AdjustmentVision AdjustmentDentalCount AdjustmentVisionCount
----------- ---------------- ---------------- --------------------- ---------------------
Ron Olsen 2500     25000    2       2
Tom Jones 25     250     2       2


  Sorry didn't see the desired results on OP post.  Great solution😊😊😊

  Dear Admin,
  Why my response is marked as answer. When Eirikur Eiriksson gives the perfect solution. Kindly remove it and mark his response as answer.

  Saravanan

• Eirikur Eiriksson

  SSC Guru

  Points: 182879

  More actions

  June 11, 2018 at 4:33 am

  #1993518

  saravanatn - Monday, June 11, 2018 4:00 AM

  saravanatn - Friday, June 8, 2018 11:42 PM

  Eirikur Eiriksson - Friday, June 8, 2018 11:22 PM

  You can simplify this query, no need to use cross apply or a CTE.
  😎

  
USE TEEST;
GO
SET NOCOUNT ON

  Declare @test-2 TABLE
(
FullName varchar(20),
AdjType int,
Adjustment int
)
INSERT @test-2 values('Tom Jones', 2, 10)
INSERT @test-2 values('Tom Jones', 2, 15)
INSERT @test-2 values('Tom Jones', 3, 100)
INSERT @test-2 values('Tom Jones', 3, 150)
INSERT @test-2 values('Ron Olsen', 2, 1000)
INSERT @test-2 values('Ron Olsen', 2, 1500)
INSERT @test-2 values('Ron Olsen', 3, 10000)
INSERT @test-2 values('Ron Olsen', 3, 15000)

  SELECT
 T.FullName
,SUM(CASE WHEN T.AdjType = 2 THEN T.Adjustment END) AS AdjustmentDental
,SUM(CASE WHEN T.AdjType = 3 THEN T.Adjustment END) AS AdjustmentVision
,SUM(CASE WHEN T.AdjType = 2 THEN 1 END) AS AdjustmentDentalCount
,SUM(CASE WHEN T.AdjType = 3 THEN 1 END) AS AdjustmentVisionCount
FROM  @test-2 T
GROUP BY T.FullName
ORDER BY T.FullName;

  Output
  
FullName  AdjustmentDental AdjustmentVision AdjustmentDentalCount AdjustmentVisionCount
----------- ---------------- ---------------- --------------------- ---------------------
Ron Olsen 2500     25000    2       2
Tom Jones 25     250     2       2


  Sorry didn't see the desired results on OP post.  Great solution😊😊😊

  Dear Admin,
  Why my response is marked as answer. When Eirikur Eiriksson gives the perfect solution. Kindly remove it and mark his response as answer.

  Don't worry about it Saravanan, tshad just made a mistake there, but it is clear what the correct answer is and your post contains that answer then is's all fine.
  😎

• saravanatn

  SSCarpal Tunnel

  Points: 4533

  More actions

  June 11, 2018 at 4:50 am

  #1993521

  Eirikur Eiriksson - Monday, June 11, 2018 4:33 AM

  saravanatn - Monday, June 11, 2018 4:00 AM

  saravanatn - Friday, June 8, 2018 11:42 PM

  Eirikur Eiriksson - Friday, June 8, 2018 11:22 PM

  You can simplify this query, no need to use cross apply or a CTE.
  😎

  
USE TEEST;
GO
SET NOCOUNT ON

  Declare @test-2 TABLE
(
FullName varchar(20),
AdjType int,
Adjustment int
)
INSERT @test-2 values('Tom Jones', 2, 10)
INSERT @test-2 values('Tom Jones', 2, 15)
INSERT @test-2 values('Tom Jones', 3, 100)
INSERT @test-2 values('Tom Jones', 3, 150)
INSERT @test-2 values('Ron Olsen', 2, 1000)
INSERT @test-2 values('Ron Olsen', 2, 1500)
INSERT @test-2 values('Ron Olsen', 3, 10000)
INSERT @test-2 values('Ron Olsen', 3, 15000)

  SELECT
 T.FullName
,SUM(CASE WHEN T.AdjType = 2 THEN T.Adjustment END) AS AdjustmentDental
,SUM(CASE WHEN T.AdjType = 3 THEN T.Adjustment END) AS AdjustmentVision
,SUM(CASE WHEN T.AdjType = 2 THEN 1 END) AS AdjustmentDentalCount
,SUM(CASE WHEN T.AdjType = 3 THEN 1 END) AS AdjustmentVisionCount
FROM  @test-2 T
GROUP BY T.FullName
ORDER BY T.FullName;

  Output
  
FullName  AdjustmentDental AdjustmentVision AdjustmentDentalCount AdjustmentVisionCount
----------- ---------------- ---------------- --------------------- ---------------------
Ron Olsen 2500     25000    2       2
Tom Jones 25     250     2       2


  Sorry didn't see the desired results on OP post.  Great solution😊😊😊

  Dear Admin,
  Why my response is marked as answer. When Eirikur Eiriksson gives the perfect solution. Kindly remove it and mark his response as answer.

  Don't worry about it Saravanan, tshad just made a mistake there, but it is clear what the correct answer is and your post contains that answer then is's all fine.
  😎

  Just curious to know how it mark it as answer for a post

  Saravanan

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  June 11, 2018 at 9:13 am

  #1993560

  saravanatn - Monday, June 11, 2018 4:50 AM

  Just curious to know how it mark it as answer for a post

  Only the original poster can do that.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• Jeff Moden

  SSC Guru

  Points: 1003851

  More actions

  June 11, 2018 at 8:26 pm

  #1993626

  tshad - Monday, June 11, 2018 1:32 AM

  I was able to use this in the actual select statement and it worked perfectly.

  Thanks.

  Like Eirikur asked, though.... do you know how it works?  After all, you're the one that will need to support it and you'll also need to do it again many times in your career.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• tshad

  SSCertifiable

  Points: 5905

  More actions

  June 12, 2018 at 12:15 pm

  #1993752

  My mistake.

  You're right. I just marked the latest one.  Didn't notice that the answer was right above it.

  And yes, I do understand how it works.  Was able to quickly add it to my actual query.

  Thanks