CODEWITHSUNDEEP
sqlmultiplication
Student214
Student214

Reputation: 29

SQL multiplying two columns from two different tables

i have two tables 

Items                                        Inventory
-----------                                  -----------
Unit_Price                                   Item_Code
Item_Code                                    Order_Num
Item_Type                                    Item_In_Stk

i am trying to calculate all the inventory items that have a unit price over $25 so im doing this by multipplying Unit_Price and Item_In_Stk

here is my code 

SELECT SUM(Unit_Price * Items_In_STk)
FROM Item, Inventory
WHERE Unit_Price > 25.00
AND Item.ITEM_CODE IN(3177,45219,23456);

i put the Item_Code numbers in the AND operator that are over 25.00 but i did the math and im getting the wrong answer. How should i rearrange this because i think the logic behind it is right

Upvotes: 1

Views: 6746

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

SELECT items.item_code, SUM(Unit_Price * Items_In_STk)
FROM Items JOIN Inventory
on items.item_code = inventory.item_code 
WHERE Unit_Price > 25.00 
AND Items.ITEM_CODE IN(3177,45219,23456)
group by items.item_code

You are missing a group by clause on item_code. Also, you were missing a join clause.

Edit: If you only need the sum for all the 3 item codes, you can do

SELECT SUM(Unit_Price * Items_In_STk)
FROM Items JOIN Inventory
on items.item_code = inventory.item_code 
WHERE Unit_Price > 25.00 
AND Items.ITEM_CODE IN(3177,45219,23456)

Upvotes: 2

Related Questions