Calculating the SUM of Quantity Price from 2 different tables

Question

I have two tables as follows  PRODUCT table  Id   Name   Price   And an ORDERITEM table  Id   OrderId   ProductId   Quantity   What I m trying to do is  calculate the subtotal price for each product  Quantity Price  then SUM the TOTAL value for the entire order    I m trying something like this  SELECT Id  SUM Quantity    select Price from Product where Id   Id   as qty FROM OrderItem o WHERE OrderId    OrderId   But of course that doesn t work     Any help appreciated   EDIT  I only want to show the grand total for the entire order  so basically the sum of Quantity Price for every row in OrderItem  Here s some sample data   Sample Data  TABLE Product  Id     Name            Price   1      Tomatoes        20 09     4      Cucumbers       27 72     5      Oranges         21 13     6      Lemons          20 05 7      Apples          12 05   Table OrderItem  Id         OrderId        ProductId        Quantity 151        883            1                22 152        883            4                11 153        883            5                8 154        883            6                62   M

User · Answer

I think this is along the lines of what you re looking for  It appears that you want to see the orderid  the subtotal for each item in the order and the total amount for the order   select o1 orderID  o1 subtotal  sum o2 UnitPrice   o2 Quantity  as order total from       select o orderID  o price   o qty as subtotal     from product p inner join orderitem o on p ProductID  o productID     where o orderID    OrderId  as o1 inner join orderitem o2 on o1 OrderID   o2 OrderID group by o1 orderID  o1 subtotal

User · Answer

i think this - including null value   0   SELECT oi id            SUM nvl oi quantity 0    nvl p price 0   AS total qty      FROM ORDERITEM oi      JOIN PRODUCT p ON p id   oi productid     WHERE oi orderid    OrderId  GROUP BY oi id

User · Answer

I had the same problem as Marko and come across a solution like this     Create a Table   CREATE TABLE tableGrandTotal   columnGrandtotal int      Create a Stored Procedure   CREATE PROCEDURE GetGrandTotal AS    Delete the  tableGrandTotal  table for another usage of the stored procedure   DROP TABLE tableGrandTotal    Create a new Table which will include just one column   CREATE TABLE tableGrandTotal   columnGrandtotal int      Insert the query which returns subtotal for each orderitem row into tableGrandTotal   INSERT INTO tableGrandTotal     SELECT oi Quantity   p Price AS columnGrandTotal         FROM OrderItem oi         JOIN Product p ON oi Id   p Id    And return the sum of columnGrandTotal from the newly created table       SELECT SUM columnGrandTotal  as  Grand Total      FROM tableGrandTotal   And just simply use the GetGrandTotal Stored Procedure to retrieve the Grand Total     EXEC GetGrandTotal

User · Answer

Use     SELECT oi orderid           SUM oi quantity   p price  AS grand total      FROM ORDERITEM oi     JOIN PRODUCT p ON p id   oi productid    WHERE oi orderid    OrderId GROUP BY oi orderid   Mind that if either oi quantity or p price is null  the SUM will return NULL

User · Answer

select orderID  sum subtotal  as order total from       select orderID  productID  price  qty  price   qty as subtotal     from product p inner join orderitem o on p id   o productID     where o orderID    orderID   t group by orderID

[sql] Calculating the SUM of (Quantity*Price) from 2 different tables

Examples related to sql

Examples related to sum