Help Needed in Groupby - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Help Needed in Groupby

Author

Topic

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2014-08-23 : 13:48:20

Hi, Below is the TestData,


With SalaryReport as (

   Select 1 as IdSalary,100 as IDMainCompany , 1000 as IDSubCompany ,200000 AS Salary, 20000 as Incentive, 500 as NoofEmployees,
    '2014-01-01' as SalaryDate union all
	 Select 2 as IdSalary,100 as IDMainCompany , 1001 as IDSubCompany ,300000 AS Salary, 40000 as Incentive, 600 as NoofEmployees,
    '2014-01-01' as SalaryDate union all
	 Select 3 as IdSalary,100 as IDMainCompany , 1002 as IDSubCompany ,400000 AS Salary, 40000 as Incentive, 1500 as NoofEmployees,
    '2014-01-01' as SalaryDate union all
	 Select 4 as IdSalary,101 as IDMainCompany , 1003 as IDSubCompany ,30000 AS Salary, 2000 as Incentive, 100 as NoofEmployees,
    '2014-01-01' as SalaryDate union all
	 Select 5 as IdSalary,102 as IDMainCompany , 1004 as IDSubCompany ,450000 AS Salary, 25000 as Incentive, 700 as NoofEmployees,
    '2014-01-01' as SalaryDate
   )

Pareamter to be passed to query:

Declare @ IDMainCompany int = 100, @Process_Date datetime = '2014-01-01'

Expected output:

Am very confused about how to use group by and bring the expected output as like above. any sample query please

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2014-08-23 : 17:22:30

Here is my my try,

DECLARE @IDMainCompany int = 100;
DECLARE @Process_Date datetime = '2014-01-01';
;With SalaryReport as (

   Select 1 as IdSalary,100 as IDMainCompany , 1000 as IDSubCompany ,200000 AS Salary, 20000 as Incentive, 500 as NoofEmployees,
    '2014-01-01' as SalaryDate ,60 as Creditscore union all
	 Select 1 as IdSalary,100 as IDMainCompany , 1001 as IDSubCompany ,300000 AS Salary, 40000 as Incentive, 600 as NoofEmployees,
    '2014-01-01' as SalaryDate ,70 as Creditscore union all
	 Select 1 as IdSalary,100 as IDMainCompany , 1002 as IDSubCompany ,400000 AS Salary, 40000 as Incentive, 1500 as NoofEmployees,
    '2014-01-01' as SalaryDate ,45 as Creditscore union all
	 Select 1 as IdSalary,101 as IDMainCompany , 1003 as IDSubCompany ,30000 AS Salary, 2000 as Incentive, 100 as NoofEmployees,
    '2014-01-01' as SalaryDate ,60 as Creditscore union all
	 Select 1 as IdSalary,102 as IDMainCompany , 1004 as IDSubCompany ,450000 AS Salary, 25000 as Incentive, 700 as NoofEmployees,
    '2014-01-01' as SalaryDate,30 as Creditscore
   )

SELECT
     CAST(SR.IdSalary	   AS VARCHAR(12)) AS IdSalary	  
    ,CAST(SR.IDMainCompany AS VARCHAR(12)) AS IDMainCompany
    ,CAST(SR.IDSubCompany  AS VARCHAR(12)) AS IDSubCompany 
    ,CAST(SR.Salary	       AS VARCHAR(12)) AS Salary	      
    ,CAST(SR.Incentive	   AS VARCHAR(12)) AS Incentive	  
    ,CAST(SR.NoofEmployees AS VARCHAR(12)) AS NoofEmployees
    ,CAST(SR.SalaryDate    AS VARCHAR(15)) AS SalaryDate
	,CAST(avg(SR.Creditscore) AS VARCHAR(15)) AS average   

FROM SalaryReport SR
WHERE SR.IDMainCompany = @IDMainCompany
AND   SR.SalaryDate    = @Process_Date
UNION ALL
SELECT
     '' AS IdSalary	
    ,'' AS IDMainCompany	
    ,'' AS IDSubCompany	
    ,'Total: ' + CAST(SUM(SR.Salary)        AS VARCHAR(12)) AS Salary	   
    ,'Total: ' + CAST(SUM(SR.Incentive)     AS VARCHAR(12)) AS Incentive	  
    ,'Total: ' + CAST(SUM(SR.NoofEmployees) AS VARCHAR(12)) AS NoofEmployees
	,'Total: ' + CAST(avg(SR.Creditscore) AS VARCHAR(12)) AS  AvgCreditscore
    ,'' AS SalaryDate

FROM SalaryReport SR
WHERE SR.IDMainCompany = @IDMainCompany
AND   DATEPART(MM, SR.SalaryDate) = DATEPART(MM, @Process_Date)
group by IDMainCompany, DATEPART(MM, SR.SalaryDate)

but am getting error as

quote:
Msg 8120, Level 16, State 1, Line 18
Column 'SalaryReport.IdSalary' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

how to get out from this group by issue. any help please

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2014-08-23 : 17:54:03

Found the reason,
by mistake i added the avg function on the first block

quote:
,CAST(avg(SR.Creditscore) AS VARCHAR(15)) AS average

I should remove the avg function. It is working now.

Is there any way other than using union all? because i have 20 columns need to include on this select statement. so i will have big line of query in my storedprocedure. any other way of doing this please?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources