CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Ayman
Ayman

Reputation: 872

Getting total amount of orders using sql query

Hi lets say i have the following tow tables

first table is Orders which consist of following columns

Order_ID  OrderDate
1          2016-01-20
2          2016-01-21

and the second table is OrderDetails which consist of following columns

Order_ID ProductID UnitPrice Quantity
1         1        5.00       1
1         3        20.00      3
2         2        10.00      2

How can i get the following out put

Order_ID  OrderDate    OrderTotalamount
 1        2016-01-20    65.00
 2        2016-01-21    20.00

Upvotes: 0

Views: 60

Answers (3)

shaddad
shaddad

Reputation: 51

YOU CAN USE

SELECT TABLE1.Order_ID, TABLE1.OrderDate, SUM(TABLE2.Quantity*TABLE2.UnitPrice) as TotalPrice
  FROM TABLE1 JOIN TABLE2 WHERE(TABLE1.Order_ID = TABLE2.Order_ID);

Upvotes: 1

fmgonzalez
fmgonzalez

Reputation: 823

You can try this

SELECT a.Order_ID, a.OrderDate, SUM(Quantity*UnitPrice) as OrderTotalamout
  FROM Orders a JOIN OrderDetains ON (a.Order_ID = OrderDetails.Order_ID) 
 GROUP BY 1, 2;

I hope it works fine for you.

Upvotes: 2

Langosta
Langosta

Reputation: 497

Would you mind trying something like this out?

SELECT ORD.Order_ID, ORD.OrderDate, SUM(ORDD.UnitPrice * ORDD.Quanity) AS OrderTotalAmount

FROM Orders ORD

INNER JOIN OrderDetails ORDD
ON ORDD.Order_ID = ORD.Order_ID

GROUP BY ORD.Order_ID, ORD.OrderDate

Upvotes: 2

Related Questions