CODEWITHSUNDEEP
sqlsum
ogarogar
ogarogar

Reputation: 345

Trying to sum part of the rows

So I have three tables Orders:

CustomerID,OrderID

Order Details;

OrderID,ProductId,UnitPrice,Quantity,Discount

And Products:

ProductID,ProductName

And I need to combine these 2 tables and create this one:

[Orderd Details].CustomersID,Products.ProductName,FORMULA

Formula is how much money people spent money on this product. So I think I have to sum UnitPrice* Quantity*(1-Discount) from every order for this product. Sadly I have no idea how should I do it. The best I did is: 

SELECT o.CustomerID,p.ProductName,SUM(od.Quantity*od.UnitPrice*(1-od.Discount)) as 'SKZ' 
FROM Customers as c, Orders as o,[Order Details] as od,Products as p 
WHERE (o.OrderID=od.OrderID AND p.ProductID=od.ProductID) 
GROUP BY od.ProductID ORDER BY o.CustomerID;

But it doesn't work.

Upvotes: 0

Views: 43

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270523

First, learn explicit JOIN syntax. Simple rule: Never use commas in the FROM clause.

Second, you should include all non-aggregated columns in the GROUP BY:

SELECT o.CustomerID, p.ProductName,
       SUM(od.Quantity * od.UnitPrice * (1 - od.Discount)) as SKZ
FROM Orders as o JOIN
     [Order Details] od 
     ON o.OrderID = od.OrderID JOIN
     Products p 
     ON p.ProductID = od.ProductID
GROUP BY o.CustomerID, p.ProductName
ORDER BY o.CustomerID;

Upvotes: 1

Related Questions