Select distinct id when using sum on another column?

Question

An order can consist of several rows and so the OrderId is not unique. How can I do a SUM and not have to GROUP BY in order for the SQL to parse correctly?

SELECT DISTINCT OrderId, SUM(ProductPrice * ProductQuantity ) as Total
FROM OrderDetails
GROUP BY OrderId

If I take off the GROUP BY line then I get

'OrderDetails.OrderId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

EDIT

My desired outcome is if an order consisted of 2 products then it should output both rows.

The group by is outputting 1 row.

Answer 1

Don't you have an order table ?

Generally orders contain of the order and details (lines of the order), so you should do a join with the master table instead. This should work.

SELECT O.OrderID, SUM(Od.ProductPrice * OD.ProductQuantity ) as Total FROM Orders AS O INNER JOIN OrderDetails AS OD ON O.orderID = OD.OrderID group by O.OrderID

Answer 2

You don't need the distinct.
You must have a group by when summing/counting with extra columns
You will get totals per order with the group by.

so
1-20.00
1-30.00
2-10.00

Will get
1-50.00
2-10.00

Answer 3

If you select a non-aggregated column (in this case OrderID) and an aggregated column (SUM(xxx)) then you'll have to group by on the non-aggregated columns...

But when you groupBy, then the OrderID is going to be distinct anyway, as it will group all the other values together for each orderID.