AdventureWorks Database: Find total cost of each order exceeds 100000

Last update on December 20 2024 11:57:33 (UTC/GMT +8 hours)

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.

salesorderid|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 --

Click to view Full table

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.