GROUP BY and SUM in inner join

• profhadoop

  Old Hand

  Points: 329

  More actions

  August 28, 2020 at 8:30 am

  #3784330

  My project contains below TABLES and sample data for it ::

  CREATE TABLE [DBO].[TBL_Budget] ( 
  BudgetId INT IDENTITY(1, 1) NOT NULL 
  ,BudgetName NVARCHAR(200) NOT NULL
  ,BudgetType INT NOT NULL
  ,BudgetAmount INT NOT NULL
  ,CONSTRAINT PK_Budget PRIMARY KEY (BudgetId)
  )
  INSERT INTO [DBO].[TBL_Budget] VALUES ('B1',101, 500000)
  INSERT INTO [DBO].[TBL_Budget] VALUES ('B2',201, 1000000)
  ---------------------------------------------------------------
  CREATE TABLE [DBO].[TBL_School] ( 
  SchoolId INT IDENTITY(1, 1) NOT NULL 
  ,SchoolName NVARCHAR(200) NOT NULL
  ,CONSTRAINT PK_School PRIMARY KEY (SchoolId)
  )
  INSERT INTO [DBO].[TBL_School] VALUES ('NJ.EBS')
  INSERT INTO [DBO].[TBL_School] VALUES ('LA.EBS') 
  ---------------------------------------------------------------
  CREATE TABLE [DBO].[TBL_Mapped] ( 
  MappedId INT IDENTITY(1, 1) NOT NULL 
  ,MappedType INT NOT NULL --- {1st quarter, 2nd quarter, 3rd quarter}
  ,MappedAmount INT NOT NULL
  ,BudgetId INT NOT NULL 
  ,SchoolId INT NOT NULL 
  ,CONSTRAINT PK_Mapped PRIMARY KEY (MappedId)
  )
  
  INSERT INTO [DBO].[TBL_Mapped] VALUES (1, 100000, 1, 1 );
  INSERT INTO [DBO].[TBL_Mapped] VALUES (1, 300000, 1, 2 )
  INSERT INTO [DBO].[TBL_Mapped] VALUES (2, 600000, 2, 2 )
  INSERT INTO [DBO].[TBL_Mapped] VALUES (2, 200000, 2, 1 )
  INSERT INTO [DBO].[TBL_Mapped] VALUES (2, 100000, 1, 1 )
  ---------------------------------------------------------------?

  I have to make a report such that how much amount from each budget is given to schools in quarterly basis. Report Format, please see the attached image.

  How to get the report format data? I have tried with this query but not getting the desired result. Also MS SQL Server does not allow to group by in the columns which are not contained in function. What will be the correct query to get the desired result? Please see the image.

  SELECT 
  A.BudgetId 
  ,A.BudgetName
  ,A.BudgetAmount
  ,B.MappedType 
  ,SUM(B.MappedAmount) [TotalMappedAmount]
  FROM [DBO].[TBL_Budget] A
  INNER JOIN [DBO].[TBL_Mapped] B ON A.BudgetId = B.BudgetId
  GROUP BY
  A.BudgetId
  ,B.MappedType

  

  Please help, I am new to SQL development. Thank You!!!

• Mr. Brian Gale

  SSC-Insane

  Points: 24690

  More actions

  August 28, 2020 at 9:03 pm

  #3784528

  I think you will want to look into windowing functions.  in this particular example, remove your GROUP BY and change your SUM to:

  ,SUM(B.MappedAmont) OVER (PARTITION BY A.BudgetId, B.MappedType) AS [TotalMappedAmount)

  basically you are breaking (partitioning) the SUM up by the budget ID and mapped type.  Still doing the same SUM, but grouping the summing up by the budget ID and mapped type.

  The above is all just my opinion on what you should do. 
  As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
  I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

• September 26, 2020 at 3:06 pm

  This was removed by the editor as SPAM