Group By Function in SQL

Kyle Witter
Kyle Witter used Ask the Experts™
on
I'm trying to create a view in MS SQL Management Studio, that groups data by two variables, but am having difficulty doing so.  As with my experience with PHP/SQL you can group by a column with little effort, but I continue to get an error that says "Column'dbo.Delivery.Job' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Basically I'm looking to remove duplicate entries in the outputted data, and if I could group by dbo.Job_Operation.Operation_Service & dbo.Job_Operation.Description it should eliminate the problem.  Attached is my current SQL Code.

Thank You!
SELECT     dbo.Delivery.Job, dbo.Delivery.Shipped_Date, dbo.Delivery.Shipped_Quantity, dbo.Job.Part_Number, dbo.Job.Description, dbo.Work_Center.Work_Center, 
                      dbo.Job_Operation.Operation_Service, dbo.Job_Operation.Description AS Route_Disc, dbo.Job_Operation.Est_Labor_Burden, 
                      dbo.Job_Operation.Est_Machine_Burden, dbo.Job_Operation.Est_GA_Burden, dbo.Job.Est_Labor, dbo.Job_Operation.Act_Labor_Burden, 
                      dbo.Job_Operation.Act_Machine_Burden, dbo.Job_Operation.Act_GA_Burden, dbo.Job.Act_Labor
FROM         dbo.Job INNER JOIN
                      dbo.Job_Operation ON dbo.Job.Job = dbo.Job_Operation.Job INNER JOIN
                      dbo.Job_Operation_Time ON dbo.Job_Operation.Job_Operation = dbo.Job_Operation_Time.Job_Operation INNER JOIN
                      dbo.Work_Center ON dbo.Job_Operation.Work_Center = dbo.Work_Center.Work_Center INNER JOIN
                      dbo.Delivery ON dbo.Job.Job = dbo.Delivery.Job

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
try
select * from (
SELECT     dbo.Delivery.Job, 
	dbo.Delivery.Shipped_Date, 
	dbo.Delivery.Shipped_Quantity, 
	dbo.Job.Part_Number, 
	dbo.Job.Description, 
	dbo.Work_Center.Work_Center, 
	dbo.Job_Operation.Operation_Service, 
	dbo.Job_Operation.Description AS Route_Disc, 
	dbo.Job_Operation.Est_Labor_Burden, 
	dbo.Job_Operation.Est_Machine_Burden, 
	dbo.Job_Operation.Est_GA_Burden, 
	dbo.Job.Est_Labor, 
	dbo.Job_Operation.Act_Labor_Burden, 
	dbo.Job_Operation.Act_Machine_Burden, 
	dbo.Job_Operation.Act_GA_Burden, 
	dbo.Job.Act_Labor,
	row_number() over (partition by dbo.Job_Operation.Operation_Service, dbo.Job_Operation.Description order by dbo.Job_Operation.Operation_Service) rn
FROM         dbo.Job 
INNER JOIN dbo.Job_Operation ON dbo.Job.Job = dbo.Job_Operation.Job 
INNER JOIN dbo.Job_Operation_Time ON dbo.Job_Operation.Job_Operation = dbo.Job_Operation_Time.Job_Operation 
INNER JOIN dbo.Work_Center ON dbo.Job_Operation.Work_Center = dbo.Work_Center.Work_Center 
INNER JOIN dbo.Delivery ON dbo.Job.Job = dbo.Delivery.Job
) a
where rn = 1

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial