CODEWITHSUNDEEP
sqlsql-servert-sql
MonuMan5
MonuMan5

Reputation: 373

Noob Concern: T-SQL Group By Clause Error

I'm not sure what I am doing wrong. This is a sample query from a T-SQL Fundamentals book that I am reading... 

SELECT empid, orderdate FROM Sales.Orders
WHERE custid = 71
GROUP BY empid, YEAR(orderdate)

I get the following error:

Msg 8120, Level 16, State 1, Line 1 Column 'Sales.Orders.orderdate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

What the heck am I doing wrong?...

Upvotes: 0

Views: 430

Answers (3)

Michał Skóra
Michał Skóra

Reputation: 371

try something like that

SELECT * FROM Sales.Orders AS o
WHERE custid = 71
GROUP BY YEAR(o.orderdate)

Upvotes: 0

ipr101
ipr101

Reputation: 24236

Try this -

SELECT empid, YEAR(orderdate) FROM Sales.Orders
WHERE custid = 71
GROUP BY empid, YEAR(orderdate)

There's an excellent blog post that explains the error you were getting and why you were getting it, here - http://weblogs.sqlteam.com/jeffs/archive/2007/07/20/but-why-must-that-column-be-contained-in-an-aggregate.aspx

Upvotes: 1

OMG Ponies
OMG Ponies

Reputation: 332561

The GROUP BY you provided is working off empid and the year of the orderdate (an INT). But the SELECT is returning empid and a DATETIME data type... Disregarding the data type, the data is different between what is being grouped & presented.

You need to use:

  SELECT o.empid, 
         YEAR(o.orderdate) 
    FROM Sales.Orders o
   WHERE o.custid = 71
GROUP BY o.empid, YEAR(o.orderdate)

...or: 

  SELECT o.empid, 
         o.orderdate 
    FROM Sales.Orders o
   WHERE o.custid = 71
GROUP BY o.empid, o.orderdate

Alternately, SQL Server allows you to reference column aliases in the GROUP BY:

  SELECT o.empid, 
         YEAR(o.orderdate) AS yr
    FROM Sales.Orders o
   WHERE o.custid = 71
GROUP BY o.empid, yr

Upvotes: 2

Related Questions