Group By Function in SQL
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.Operatio
Thank You!
Basically I'm looking to remove duplicate entries in the outputted data, and if I could group by dbo.Job_Operation.Operatio
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
Do more with
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
Premium Content
You need an Expert Office subscription to comment.Start Free Trial