AdventureWorks Database: Find total cost of each order exceeds 100000
27. From the following table write a query in SQL to retrieve the total cost of each salesorderID that exceeds 100000. Return SalesOrderID, total cost.
Sample table: Sales.SalesOrderDetailsalesorderid|salesorderdetailid|carriertrackingnumber|orderqty|productid|specialofferid|unitprice|unitpricediscount|rowguid |modifieddate | ------------+------------------+---------------------+--------+---------+--------------+---------+-----------------+------------------------------------+-----------------------+ 43659| 1|4911-403C-98 | 1| 776| 1| 2024.994| 0|b207c96d-d9e6-402b-8470-2cc176c42283|2011-05-31 00:00:00.000| 43659| 2|4911-403C-98 | 3| 777| 1| 2024.994| 0|7abb600d-1e77-41be-9fe5-b9142cfc08fa|2011-05-31 00:00:00.000| 43659| 3|4911-403C-98 | 1| 778| 1| 2024.994| 0|475cf8c6-49f6-486e-b0ad-afc6a50cdd2f|2011-05-31 00:00:00.000| 43659| 4|4911-403C-98 | 1| 771| 1| 2039.994| 0|04c4de91-5815-45d6-8670-f462719fbce3|2011-05-31 00:00:00.000| 43659| 5|4911-403C-98 | 1| 772| 1| 2039.994| 0|5a74c7d2-e641-438e-a7ac-37bf23280301|2011-05-31 00:00:00.000| 43659| 6|4911-403C-98 | 2| 773| 1| 2039.994| 0|ce472532-a4c0-45ba-816e-eefd3fd848b3|2011-05-31 00:00:00.000| 43659| 7|4911-403C-98 | 1| 774| 1| 2039.994| 0|80667840-f962-4ee3-96e0-aeca108e0d4f|2011-05-31 00:00:00.000| 43659| 8|4911-403C-98 | 3| 714| 1| 28.8404| 0|e9d54907-e7b7-4969-80d9-76ba69f8a836|2011-05-31 00:00:00.000| 43659| 9|4911-403C-98 | 1| 716| 1| 28.8404| 0|aa542630-bdcd-4ce5-89a0-c1bf82747725|2011-05-31 00:00:00.000| 43659| 10|4911-403C-98 | 6| 709| 1| 5.7| 0|ac769034-3c2f-495c-a5a7-3b71cdb25d4e|2011-05-31 00:00:00.000| 43659| 11|4911-403C-98 | 2| 712| 1| 5.1865| 0|06a66921-6b9f-4199-a912-ddafd383472b|2011-05-31 00:00:00.000| 43659| 12|4911-403C-98 | 4| 711| 1| 20.1865| 0|0e371ee3-253e-4bb0-b813-83cf4224f972|2011-05-31 00:00:00.000| -- more --
Sample Solution:
SELECT
SalesOrderID, -- Selecting the SalesOrderID column
SUM(orderqty*unitprice) AS OrderIDCost -- Calculating the total cost (orderqty * unitprice) for each SalesOrderID and aliasing it as OrderIDCost
FROM
Sales.SalesOrderDetail -- Selecting data from the SalesOrderDetail table
GROUP BY
SalesOrderID -- Grouping the results by SalesOrderID
HAVING
SUM(orderqty*unitprice) > 100000.00 -- Filtering groups where the total cost is greater than 100000.00
ORDER BY
SalesOrderID; -- Sorting the results by SalesOrderID
Explanation:
- This SQL query retrieves the total cost of each sales order from the SalesOrderDetail table.
- It calculates the total cost by multiplying the order quantity (orderqty) with the unit price (unitprice) for each line item and summing them up for each sales order.
- The GROUP BY clause groups the results by SalesOrderID.
- The HAVING clause filters the groups to include only those with a total cost greater than 100000.00.
- Finally, the results are ordered by SalesOrderID.
Sample Output:
salesorderid|orderidcost| ------------+-----------+ 43875|122744.1667| 43884|116248.4897| 44518|127099.9957| 44528|108783.5872| 44530|104960.0101| 44795|104588.7130| 46066|100378.9078| 46067|101857.2130| 46607|121037.4456| 46616|153432.0611| 46643|110830.3608| 46645|101373.1246| 46660|119415.1966| 46981|149533.7021| 47018|107991.0123| 47027|105375.2251| 47355|130185.6757| ...
SQL AdventureWorks Editor:
Practice Online
Contribute your code and comments through Disqus.
Previous: Find sum, avg, OrderQty for specific orders.
Next: Products whose names start with Lock Washer.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics