eRROR MESSAGE IN sql qUERY - 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

Other SQL Server 2008 Topics

eRROR MESSAGE IN sql qUERY

Author

Topic

SATELE01
Starting Member

1 Post

Posted - 2012-08-30 : 13:24:10

I'M GETTING THE BELOW ERROR MESSAGES IN THE QUERY BELOW. aNY GUIDANACE WOULD BE APPRECIATED. I'M A NEW USER OF SQL MANAGEMENT TUDIO 2008 R2

Msg 8120, Level 16, State 1, Line 16
Column 'dbo.dt_Employee_Payroll_Detail.Pay_Date' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 17
Column 'dbo.dt_Employee_Payroll_Detail.Pay_Class' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

select distinct
a1.Client_ID,
a3.Client_Legal_Name,
a4.employee_first_name,
a4.employee_MI,
a4.Employee_Last_Name,
a2.Employee_SSN,
a4.Employee_Address1,
a4.Employee_Address2,
a4.Employee_City,
a4.Employee_State,
a4.Employee_Zip_Code,
a4.Employee_Birth_Date,
a2.Hire_Date,
a2.Termination_Date,
'Gross Salary' = case when a1.Pay_Date = '2012-08-24'
and a1.Pay_Class = 'e'
then sum(a1.LineItemAmount)
else 0
end
from dbo.dt_Employee_Payroll_Detail (nolock) a1,
dbo.dt_Employee_Client (nolock) a2,
dbo.Client_Master (nolock) a3,
dbo.Employee a4
where a1.Client_ID = a2.Client_ID
and a1.Client_ID = a3.client_id
and a1.Employee_ID = a2.employee_id
and a1.Employee_ID = a4.employee_id
and a1.Pay_Date between '2012-01-01' and '2012-08-24'
--and a1.employee_id = 'r14257'
and a1.Client_ID = '10981001'
group by
a1.Client_ID,
a3.Client_Legal_Name,
a4.employee_first_name,
a4.employee_MI,
a4.Employee_Last_Name,
a2.Employee_SSN,
a4.Employee_Address1,
a4.Employee_Address2,
a4.Employee_City,
a4.Employee_State,
a4.Employee_Zip_Code,
a4.Employee_Birth_Date,
a2.Hire_Date,
a2.Termination_Date

chadmat
The Chadinator

1974 Posts

Posted - 2012-08-30 : 13:50:31

Pretty self explanitory. You can't use those 2 columns in the select list if you don't also use them in the group by, or and aggregate function (Sum, max etc...)

-Chad

prett
Posting Yak Master

212 Posts

Posted - 2012-08-31 : 00:00:13

When you use group by, each column in the select clause must be either defined via an Aggregate function (like MAX, MIN, AVG, ...) or must be included in the group by clause.

Please follow this link to solve the problem: http://sqlzoo.net/howto/source/u.cgi/err979/sqlserver

adolfmitchel
Starting Member

3 Posts

Posted - 2014-10-26 : 14:03:51

Please check this link to solve the issue: Recovery Toolbox for SQL Server

unspammed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources