SQLTeam.com | Weblogs | Forums

Combine / Sum field in Query?

New SQL user here, have a probably simple question about querying.

Is it possible to run a query that will "combine" rows containing the same value in a field and summing up another field for those rows?

Ex Say we have 3 fields - Order date, Sales Order #, Part Number, Line Price.

Sales Order# appears multiple times because there are more than one part number on a particular sales order.

I would want to query where it takes any sales order that is the SAME and it puts that in the table only once, and it sums up the line price (to give a total order price). The other fields could just be "Keep 1st" type thing. Is this possible?

I normally do this in excel but when I have tons of rows this doesn't cooperate very well.

Please provide some sample data as examples.

Very simple data would be like:

Order Date, Sales Order Number, Part Number, Line Price
5/5/2020, 0001, Part1, 5.00
5/6/2020, 0001, Part2, 10.00
5/10/2020, 0002, Part1, 5.00

Results would be
5/5/2020, 0001, Part1, 15.00
5/10/2020, 0002, Part2, 5.00

Basically each line is a line number on an order, the part numbers are unique but would appear more than once when there are multiple lines (parts) on the order.

I want a unique list by order number but total sales.

It's very basic - I could have sworn I learned how this works when I was studying but just cannot recall. Maybe need to go back through a refresher course, just thought it was be something simple.

I'll try to work up an actual sample to attach.

If you're on a SQL version that supports FIRST_VALUE, then:

    MIN([Order Date]) AS [Order Date],
    [Sales Order Number],
    FIRST_VALUE([Part Number]) OVER(PARTITION BY [Sales Order Number] 
        ORDER BY [Order Date]) AS [Part Number],
    SUM([Line Price]) AS [Total Line Price] 
FROM dbo.table_name
    [Sales Order Number]
1 Like