CODEWITHSUNDEEP
sql-serversql-server-2008
Doan Van Thang
Doan Van Thang

Reputation: 997

Merge multiple rows with same value into a single row

I'm doing some exercise with northwind database and I have written the following query :

select 
    Employees.EmployeeID, year(Orders.OrderDate) as Year, 
    count(Employees.EmployeeID) as 'Total Orders'
from  
    Orders 
join 
    Employees on Orders.EmployeeID = Employees.EmployeeID
where 
    year(Orders.OrderDate) = '1996'
group by 
    Employees.EmployeeID, Orders.OrderDate
order by 
    Employees.EmployeeID asc

I get the following output:

EmployeeID | Year | Total Orders
-----------+------+--------------
1          |1996  | 1
1          |1996  | 1
1          |1996  | 2
1          |1996  | 1
1          |1996  | 1
....

I think the problem is year(Orders.OrderDate) part because original date have month, day,...

Any help with be very much appreciated!

Upvotes: 0

Views: 43

Answers (2)

Santhana
Santhana

Reputation: 417

whenever using group by clause, We should use the all the fields without alias name from select clause as it is except aggregate operations.

In your case, your group by clause should be

group by Employees.EmployeeID, year(Orders.OrderDate)

Upvotes: 0

C. Rush
C. Rush

Reputation: 62

Try:

group by Employees.EmployeeID, year(Orders.OrderDate)

As far as I know, you can group by a function.

Edit: Also, you generally can't group by an alias representing a function unless it's evaluated first in a sub query.

Upvotes: 1

Related Questions