TesteroniPeperoni TesteroniPeperoni - 6 months ago 13
SQL Question

SQL Using Multiply 3 Times with different ID's

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


Also have tried inserting SELECT inside () but then the values that I get are wrong and creating one more inner join inside doesn't seem promising

Answer

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

Comments