Catalin Cernat - 1 year ago 84
Vb.net Question

# Select Distinct Sum - GROUP BY

``````SELECT DISTINCT [SC Vermorel SRL\$Production Order].No_,
[SC Vermorel SRL\$Production Order].[Replan Ref_ No_],
[SC Vermorel SRL\$Production Order].Description,
[SC Vermorel SRL\$Production Order].[Source No_],
[SC Vermorel SRL\$Production Order].[Routing No_],
[SC Vermorel SRL\$Production Order].Quantity,
[SC Vermorel SRL\$Production Order].[Old Prod_ Order No_],
[SC Vermorel SRL\$Routing Line].[Run Time],
[SC Vermorel SRL\$Routing Line].[Run Time] * [SC Vermorel SRL\$Production Order].Quantity AS TotalOre,
[SC Vermorel SRL\$Prod_ Order Line].[Finished Quantity],
SUM([SC Vermorel SRL\$ProductieZilnica].[Cantitate sudura]) AS [Cantitate sudura],
SUM([SC Vermorel SRL\$ProductieZilnica].[Cantitate montaj]) AS [Cantitate montaj]
FROM [SC Vermorel SRL\$Production Order]
INNER JOIN [SC Vermorel SRL\$Routing Line]
ON [SC Vermorel SRL\$Production Order].[Routing No_] = [SC Vermorel SRL\$Routing Line].[Routing No_]
INNER JOIN [SC Vermorel SRL\$Prod_ Order Line]
ON [SC Vermorel SRL\$Production Order].No_ = [SC Vermorel SRL\$Prod_ Order Line].[Prod_ Order No_]
INNER JOIN [SC Vermorel SRL\$ProductieZilnica]
ON [SC Vermorel SRL\$Production Order].No_ = [SC Vermorel SRL\$ProductieZilnica].RPO
WHERE ([SC Vermorel SRL\$Production Order].Status = 3)
AND ([SC Vermorel SRL\$Production Order].[Location Code] = 'MACH FIN')
AND ([SC Vermorel SRL\$Production Order].[Gen_ Prod_ Posting Group] = 'FINISHED G')
AND ([SC Vermorel SRL\$Routing Line].No_ = '150')
GROUP BY [SC Vermorel SRL\$Production Order].No_
``````

I need to attach the sum of all the entries from another table (
`[SC Vermorel SRL\$ProductieZilnica]`
) which corresponds to each distinct
`No_`
from
`[SC Vermorel SRL\$Production Order]`
table.

I cant for the love of God make it work. Can someone point me in the right direction?

At first, use table aliases - it will make your query more readable.

If you are using GROUP BY you need to add all columns, except one that are in aggregation function (you got SUM) like:

``````SELECT  PO.No_,
PO.[Replan Ref_ No_],
PO.[Description],
PO.[Source No_],
PO.[Routing No_],
PO.Quantity,
PO.[Old Prod_ Order No_],
RL.[Run Time],
RL.[Run Time] * PO.Quantity AS TotalOre,
POL.[Finished Quantity],
SUM(PZ.[Cantitate sudura]) AS [Cantitate sudura],
SUM(PZ.[Cantitate montaj]) AS [Cantitate montaj]
FROM  [SC Vermorel SRL\$Production Order] PO
INNER JOIN [SC Vermorel SRL\$Routing Line] RL
ON PO.[Routing No_] = RL.[Routing No_]
INNER JOIN [SC Vermorel SRL\$Prod_ Order Line] POL
ON PO.No_ = POL.[Prod_ Order No_]
INNER JOIN [SC Vermorel SRL\$ProductieZilnica] PZ
ON PO.No_ = PZ.RPO
WHERE     (PO.Status = 3) AND (PO.[Location Code] = 'MACH FIN') AND
(PO.[Gen_ Prod_ Posting Group] = 'FINISHED G') AND (RL.No_ = '150')
GROUP BY PO.No_,
PO.[Replan Ref_ No_],
PO.[Description],
PO.[Source No_],
PO.[Routing No_],
PO.Quantity,
PO.[Old Prod_ Order No_],
RL.[Run Time],
PO.Quantity,
POL.[Finished Quantity]
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download