You are here:

MS SQL Server/Left join with groups

Advertisement


Question
Hi,
I am trying to perform a select with left join along with group by clause but i keep getting the message
"Column 'Orders.OrderID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. "

the query i am trying is

SELECT Customers.ContactName,Orders.OrderID,Orders.OrderDate
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
Group By Customers.ContactName;

i am using the standard Northwind sample database as the database,
do help..

Answer
Hello Prakash,

GROUP BY requires all columns in the select to either have those mentioned in the GROUP BY clause or have aggregate function with those in the SELECT.

In your query, there are three columns in the select and there are no columns with aggregate (SUM, AVG, MAX etc.), while there's only one column in the GROUP BY. This mandates the rest two columns of SELECT to either have aggregate on those or be in the group by or to be removed from SELECT totally.

Feel free to followup.

Regards,

MS SQL Server

All Answers


Answers by Expert:


Ask Experts

Volunteer


Jwalant Natvarlal Soneji

Expertise

From general queries about SQL Severs both 2000 and 2005 to advanced features like Integration Services and Reporting Services.

Experience

Experience in the area: I have been working with database triggers, stored procedures, views, function, joins, dts, Query optimization. Education/Credentials: BE IT - 2005 Batch with First Class.

Publications
http://jwalantsoneji.com

Education/Credentials
BE IT, India

Awards and Honors
MCP

©2016 About.com. All rights reserved.