CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Alex
Alex

Reputation: 649

Column is invalid in the select list due to it is not contained in either an aggregate function or the GROUP BY clause

I'm trying to get the order count by each month each year, I found that there are several related functions which is helpful.

First are datepart(M, OrderDate), datepart(Y, OrderDate), so I wrote my code as follows:

select DATEPART(Q, OrderDate) Q , datepart(YEAR, OrderDate) Y, count(*) count 
from Orders 
group by DATEPART(Q, OrderDate), datepart(Y, OrderDate);

but it has error: Column 'Orders.OrderDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Then, I tried to use Month(DateOrder), and Year(DateOrder) and the code is as follows:

select month(OrderDate) M , year(OrderDate) y, count(*) count 
from Orders 
group by month(OrderDate),year(OrderDate);

the result of the above code shows it is success.

My question is why the first code has error but the second code is correct? Thanks!

Upvotes: 0

Views: 771

Answers (1)

Nenad Zivkovic
Nenad Zivkovic

Reputation: 18559

Because in SELECT you have used datepart(YEAR, OrderDate) searching for YEAR part, and in group by you have datepart(Y, OrderDate) - Y is not short from YEAR but actually stand for DayOfYear

https://learn.microsoft.com/en-us/sql/t-sql/functions/datepart-transact-sql

Short for Year is YY

Query would work as :

select DATEPART(Q, OrderDate) Q , datepart(YEAR, OrderDate) Y, count(*) count 
from Orders 
group by DATEPART(Q, OrderDate), datepart(YY, OrderDate);

You can see in this DEMO - using YY will give 2017 for today, using Y will give 230 - as it's 230th day in this year

Upvotes: 2

Related Questions