AdventureWorks Database: Find sum, avg, OrderQty for specific orders

SQL Query - AdventureWorks: Exercise-26 with Solution

26. From the following table write a query in SQL to find the sum, average, and number of order quantity for those orders whose ids are 43659 and 43664 and product id starting with '71'. Return SalesOrderID, OrderNumber,ProductID, OrderQty, sum, average, and number of order quantity.

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 AS OrderNumber, -- Selecting the SalesOrderID column and renaming it as OrderNumber
    ProductID, -- Selecting the ProductID column
    OrderQty AS Quantity, -- Selecting the OrderQty column and renaming it as Quantity
    SUM(OrderQty) OVER (ORDER BY SalesOrderID, ProductID) AS Total, -- Calculating the cumulative sum of OrderQty over ordered SalesOrderID and ProductID
    AVG(OrderQty) OVER(PARTITION BY SalesOrderID ORDER BY SalesOrderID, ProductID) AS Avg, -- Calculating the average of OrderQty partitioned by SalesOrderID and ordered by SalesOrderID and ProductID
    COUNT(OrderQty) OVER(ORDER BY SalesOrderID, ProductID ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING) AS Count -- Calculating the count of OrderQty over ordered SalesOrderID and ProductID with a window frame of one row before and one row after the current row
FROM 
    Sales.SalesOrderDetail
WHERE 
    SalesOrderID IN(43659,43664) and CAST(ProductID AS TEXT) LIKE '71%'; -- Filtering the rows where SalesOrderID is either 43659 or 43664 and ProductID starts with '71'

Explanation:

• This SQL query retrieves information about sales orders and products from the SalesOrderDetail table.
• It calculates various window functions over the data.
• The SUM function calculates the cumulative sum of the order quantities over ordered SalesOrderID and ProductID.
• The AVG function calculates the average order quantity partitioned by SalesOrderID and ordered by SalesOrderID and ProductID.
• The COUNT function calculates the count of order quantities over ordered SalesOrderID and ProductID with a window frame of one row before and one row after the current row.
• Rows are filtered based on specific SalesOrderIDs (43659 and 43664) and ProductIDs starting with 71%.

Sample Output:

ordernumber|productid|quantity|total|avg                   |count|
-----------+---------+--------+-----+----------------------+-----+
      43659|      711|       4|    4|    4.0000000000000000|    2|
      43659|      712|       2|    6|    3.0000000000000000|    3|
      43659|      714|       3|    9|    3.0000000000000000|    4|
      43659|      716|       1|   10|    2.5000000000000000|    5|
      43664|      714|       1|   11|1.00000000000000000000|    6|
      43664|      716|       1|   12|1.00000000000000000000|    6|

SQL AdventureWorks Editor:

Practice Online

Contribute your code and comments through Disqus.

Previous: Find sum, avg, count, min, max OrderQty.
Next: Find total cost of each order exceeds 100000.