SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Help Needed in Groupby
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sqllover
Constraint Violating Yak Guru

India
334 Posts

Posted - 08/23/2014 :  13:48:20  Show Profile  Reply with Quote
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

India
334 Posts

Posted - 08/23/2014 :  17:22:30  Show Profile  Reply with Quote
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
Go to Top of Page

sqllover
Constraint Violating Yak Guru

India
334 Posts

Posted - 08/23/2014 :  17:54:03  Show Profile  Reply with Quote
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?
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000