Finding total invoice amount

Question

I need some help finding the sum of all rows grouped by orderid. Each row is a different product purchased on an order. Some orders had more than one product purchased (hence why there are multiple rows per orderid). The total column is the total $ for each orderid.

Order Details table (full)

Current Results

SELECT OrderID, SUM(UnitPrice*Quantity*(1-Discount)) OVER (PARTITION BY OrderID) as Total_Invoice
FROM [Order Details]
ORDER BY OrderID

How do I get only one row for each invoice? So results looks like this:

| order_id | total_invoice          |
| -------: | ---------------------: |
| 10248    | 440                    |
| 10249    | 1863.39999389648       |
| 10250    | 1552.60003662109       |
| 10251    | 654.060005187988       |
| 10252    | 3597.89990234375       |

Answer 1

I would put Distinct right after the select. I would do this because you have sum over partition by

Your current code:

SELECT OrderID, SUM(UnitPrice*Quantity*(1-Discount)) OVER (PARTITION BY OrderID) as Total_Invoice
FROM [Order Details]
ORDER BY OrderID

I would change this to:

SELECT DISTINCT OrderID, SUM(UnitPrice*Quantity*(1-Discount)) OVER (PARTITION BY OrderID) as Total_Invoice
FROM [Order Details]
ORDER BY OrderID

If you did not want the partition by, you could do this:

SELECT OrderID, SUM(UnitPrice*Quantity*(1-Discount)) as Total_Invoice
FROM [Order Details]
GROUP BY OrderID
ORDER BY OrderID

Answer 2

I'm not familiar with the northwind data model, but you might find the figure you want is in the order header table not the order detail table.

If not, not need to use windowing functions, this should get you what you want.

SELECT OrderID, SUM(UnitPrice*Quantity*(1-Discount)) as Total_Invoice
FROM [Order Details]
GROUP BY OrderID
ORDER BY OrderID

or a less desirable (in my opinion) option, given the data, is :

SELECT DISTINCT OrderID, Total_Invoice
FROM [Order Details]
ORDER BY OrderID