Group By issues - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Group By issues

Author

Topic

cardullo4321
Starting Member

40 Posts

Posted - 2011-09-14 : 08:10:40

I do not need to Group By two columns called Bonus% and Bonus_Plan_Type_Desc. But since I referenced them in my calculations I have to use them in my Group By statement. Is there anything I can do to get around this? I am getting 2700 rounds of data but without those two columns in my group by statement, I should get around 900 rows of data.

DECLARE @Payfile dec(18,2), @BaseAccrueDays dec(18,4), @Accrual_Month# dec(18,2)
,@PayoutRate dec(18,4), @EntityLookup varchar(20), @Accrual_Month varchar(20)

SET @Payfile = (select top 1 [BonusLookupData].[NumberPayFile]
From [BonusLookupData]
Where '1' = [BonusLookupData].[Key])

SET @BaseAccrueDays = (select top 1 [BonusLookupData].[Base_Accrued_Days]
From [BonusLookupData]
Where '1' = [BonusLookupData].[Key])

SET @Accrual_Month# = (select top 1 [BonusLookupData].[Accrual_Month#]
From [BonusLookupData]
Where '1' = [BonusLookupData].[Key])

SET @Accrual_Month = (select top 1 [BonusLookupData].[Accrual_Month]
From [BonusLookupData]
Where '1' = [BonusLookupData].[Key])

SELECT Distinct [Entity]
,Account =
Case
When Dept <> '450' Then '41140'
Else '42140'
End

,[Dept]
,[SubDept]

,[DEBIT CIP Annual] = round(((((sum([Eligible_Earnings]) / @Payfile) * (.0712328767123288))
* (@BaseAccrueDays * @Accrual_Month#)) * ([Bonus%]/100))
* (Case
When [Bonus_Plan_Type_Desc] like '%CIP%'
Then 1-(select top 1 [Entity_Lookup].[Payout%]
From [Entity_Lookup]
Where [EligibleEarningsYTD].Entity = [Entity_Lookup].Entity)
Else 0
END) ,2)
,[DEBIT CIP Annual] = ''
,[Description] = 'BONUS: CIP Annual Bonus YTD Accrual ' + @Accrual_Month

,[Bonus%]
,[Bonus_Plan_Type_Desc]

FROM [SSIS_Database].[dbo].[EligibleEarningsYTD]
Where [Bonus_Plan_Type_Desc] like '%CIP%' and [Eligible_Earnings] <> 0
Group by [Entity]
,[Dept]
,[SubDept]
,[Bonus%]
,[Bonus_Plan_Type_Desc]

Order by [Entity]
,[Dept]
,[SubDept]

Gregory Cardullo

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-14 : 08:15:10

so if you're removing them from group by based on other items in group there can be multiple instances of them in same group. in that case which value should you want to retrieve?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cardullo4321
Starting Member

40 Posts

Posted - 2011-09-14 : 08:37:19

If I remove them, I get a aggregate error because they have to be in my group by statement. I need to create a journal entry to sum by Entity, Dept, SubDept. People in the same entity, dept, and sundept can have different Bonus% and BonusDescription. However, since Bonus% and BonusDescription to calculate my DEBIT CIP Annual, I have to use it in my Group By.

Gregory Cardullo

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-14 : 08:48:25

ok. then there's no way. if you want individual bonus values you need to add them in group by. Alternatively you can exclude them in group by but add a MIN() or MAX() over them in select to retrieve max or min value in a group

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cardullo4321
Starting Member

40 Posts

Posted - 2011-09-14 : 09:55:32

I only need Bonus% and BonusDescription in my calculation but I do not need to Group by statement. So there is no way around it? There may be people in the same entity, dept, and sub department that may have different Bonus% and each different Bonus% is going to add another row in my query which is what I do not want.

Gregory Cardullo

cardullo4321
Starting Member

40 Posts

Posted - 2011-09-14 : 09:59:11

I can see where you are confused. This is my query that I want. I removed the Bonus% and BonusDescription from my query and it is only showing in my calculation. I do not want to group those two columns.

DECLARE @Payfile dec(18,2), @BaseAccrueDays dec(18,4), @Accrual_Month# dec(18,2)
,@PayoutRate dec(18,4), @EntityLookup varchar(20), @Accrual_Month varchar(20)

SET @Payfile = (select top 1 [BonusLookupData].[NumberPayFile]
From [BonusLookupData]
Where '1' = [BonusLookupData].[Key])

SET @BaseAccrueDays = (select top 1 [BonusLookupData].[Base_Accrued_Days]
From [BonusLookupData]
Where '1' = [BonusLookupData].[Key])

SET @Accrual_Month# = (select top 1 [BonusLookupData].[Accrual_Month#]
From [BonusLookupData]
Where '1' = [BonusLookupData].[Key])

SET @Accrual_Month = (select top 1 [BonusLookupData].[Accrual_Month]
From [BonusLookupData]
Where '1' = [BonusLookupData].[Key])

SELECT Distinct [Entity]
,Account =
Case
When Dept <> '450' Then '41140'
Else '42140'
End

,[Dept]
,[SubDept]

,[DEBIT CIP Annual] = round(((((sum([Eligible_Earnings]) / @Payfile) * (.0712328767123288))
* (@BaseAccrueDays * @Accrual_Month#)) * ([Bonus%]/100))
* (Case
When [Bonus_Plan_Type_Desc] like '%CIP%'
Then 1-(select top 1 [Entity_Lookup].[Payout%]
From [Entity_Lookup]
Where [EligibleEarningsYTD].Entity = [Entity_Lookup].Entity)
Else 0
END) ,2)
,[DEBIT CIP Annual] = ''
,[Description] = 'BONUS: CIP Annual Bonus YTD Accrual ' + @Accrual_Month

FROM [SSIS_Database].[dbo].[EligibleEarningsYTD]
Where [Bonus_Plan_Type_Desc] like '%CIP%' and [Eligible_Earnings] <> 0
Group by [Entity]
,[Dept]
,[SubDept]
,[Bonus%]
,[Bonus_Plan_Type_Desc]

Order by [Entity]
,[Dept]
,[SubDept]

Gregory Cardullo

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-14 : 10:37:16

so are you saying you dont want to display them? then last query is fine

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-14 : 10:37:16

cardullo4321
Starting Member

40 Posts

Posted - 2011-09-14 : 11:00:42

Yes. I do not need those two columns. My query does work but I really only want to group by Entity, Dept, and SubDept and not by Bonus% and Bonus_Plan_Type_Desc. I have to use them in the group by statement since they are used in my Debit CIP Annual Calculation. Right now since I have them grouped I am getting 2700 rows of data instead of 900 rows of data.

Group by [Entity]
,[Dept]
,[SubDept]

Gregory Cardullo

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-14 : 11:03:31

you need to put the case inside sum() and then it should work fine

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources