Catalin Cernat Catalin Cernat - 2 months ago 14
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?

Answer

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]
Comments