I have tables called Products and ProductsDetails. I want to get something like the price of an order. So let's say I want 5 pairs of "Headphonesv1" ( Comparing with not ID but name, since name could change ), 2 packs of "GumOrbit" and 7 packs of "crisps". Pair of headphonesv1 costs 10$, gum 1$ and crisps 2$. So the answer that I should get is bill ID, Bill date, and TotalCost which is = 66. My question is how do I make multiple calculations? The code that I've been trying with one at least but I get syntax error:
SELECT Products.billID, Products.Date, (ProductsDetails.Price * 5 WHERE ProductsDetails.name LIKE 'Headphonesv1')
FROM Products INNER JOIN ProductsDetails ON Products.billdID = ProductsDetails.billID
I think if you just want to see that total cost for multiple items you can use a aggregate and case expression to get the SUM.
SELECT Products.billID, Products.Date, SUM(CASE WHEN ProductsDetails.name LIKE 'Headphonesv1' THEN ProductsDetails.Price * 5 WHEN ProductsDetails.name LIKE 'GumOrbit' THEN ProductsDetails.Price * 2 WHEN ProductsDetails.name LIKE 'crisps' THEN ProductsDetails.Price * 7 END) TotalCost FROM Products INNER JOIN ProductsDetails ON Products.billdID = ProductsDetails.billID GROUP BY Products.billID, Products.Date
this seems very hard coded to serve much use though