AdventureWorks Database: Find sum, avg, count, min, max OrderQty

Last update on June 03 2024 13:24:12 (UTC/GMT +8 hours)

SQL Query - AdventureWorks: Exercise-25 with Solution

25. From the following table write a query in SQL to find the sum, average, count, minimum, and maximum order quentity for those orders whose id are 43659 and 43664. Return SalesOrderID, ProductID, OrderQty, sum, average, count, max, and min order quantity.

Sample table: Sales.SalesOrderDetail

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:

-- Selecting specific columns from the Sales.SalesOrderDetail table and performing window functions on them
SELECT 
    SalesOrderID,
    ProductID,
    OrderQty,
    -- Using the SUM window function to calculate the total quantity of orders within each partition (SalesOrderID)
    SUM(OrderQty) OVER win AS "Total Quantity",
    -- Using the AVG window function to calculate the average quantity of orders within each partition (SalesOrderID)
    AVG(OrderQty) OVER win AS "Avg Quantity",
    -- Using the COUNT window function to count the number of orders within each partition (SalesOrderID)
    COUNT(OrderQty) OVER win AS "No of Orders",
    -- Using the MIN window function to find the minimum quantity of orders within each partition (SalesOrderID)
    MIN(OrderQty) OVER win AS "Min Quantity",
    -- Using the MAX window function to find the maximum quantity of orders within each partition (SalesOrderID)
    MAX(OrderQty) OVER win AS "Max Quantity"
-- Filtering the rows to include only the specified SalesOrderID values
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN(43659, 43664)
-- Defining the window function with the PARTITION BY clause based on the SalesOrderID column
WINDOW win AS (PARTITION BY SalesOrderID);

Explanation:

The SELECT statement retrieves specific columns from the Sales.SalesOrderDetail table and performs window functions on them.
SUM(OrderQty) OVER win AS "Total Quantity": Calculates the total quantity of orders within each partition (SalesOrderID) using the SUM window function.
AVG(OrderQty) OVER win AS "Avg Quantity": Calculates the average quantity of orders within each partition (SalesOrderID) using the AVG window function.
COUNT(OrderQty) OVER win AS "No of Orders": Counts the number of orders within each partition (SalesOrderID) using the COUNT window function.
MIN(OrderQty) OVER win AS "Min Quantity": Finds the minimum quantity of orders within each partition (SalesOrderID) using the MIN window function.
MAX(OrderQty) OVER win AS "Max Quantity": Finds the maximum quantity of orders within each partition (SalesOrderID) using the MAX window function.
The WHERE clause filters the rows to include only the specified SalesOrderID values (43659 and 43664).
The WINDOW clause defines the window function with the PARTITION BY clause based on the SalesOrderID column, creating partitions for each unique SalesOrderID.

-- Selecting specific columns from the SalesOrderDetail table along with window functions applied
SELECT 
    SalesOrderID, -- Selecting the SalesOrderID column
    ProductID, -- Selecting the ProductID column
    OrderQty, -- Selecting the OrderQty column
    -- Calculating the sum of OrderQty for each SalesOrderID partition and aliasing it as "Total Quantity"
    SUM(OrderQty) OVER (PARTITION BY SalesOrderID) AS "Total Quantity",
    -- Calculating the average of OrderQty for each SalesOrderID partition and aliasing it as "Avg Quantity"
    AVG(OrderQty) OVER (PARTITION BY SalesOrderID) AS "Avg Quantity",
    -- Counting the number of occurrences of OrderQty for each SalesOrderID partition and aliasing it as "No of Orders"
    COUNT(OrderQty) OVER (PARTITION BY SalesOrderID) AS "No of Orders",
    -- Finding the minimum value of OrderQty for each SalesOrderID partition and aliasing it as "Min Quantity"
    MIN(OrderQty) OVER (PARTITION BY SalesOrderID) AS "Min Quantity",
    -- Finding the maximum value of OrderQty for each SalesOrderID partition and aliasing it as "Max Quantity"
    MAX(OrderQty) OVER (PARTITION BY SalesOrderID) AS "Max Quantity"
FROM 
    Sales.SalesOrderDetail -- Selecting data from the SalesOrderDetail table
WHERE 
    SalesOrderID IN (43659,43664); -- Filtering the rows where SalesOrderID is either 43659 or 43664

Explanation:

The SELECT statement retrieves specific columns from the SalesOrderDetail table along with window functions applied to them.
SUM(OrderQty) OVER (PARTITION BY SalesOrderID) AS "Total Quantity": Calculates the sum of OrderQty for each partition defined by SalesOrderID and renames it as "Total Quantity".
AVG(OrderQty) OVER (PARTITION BY SalesOrderID) AS "Avg Quantity": Calculates the average of OrderQty for each partition defined by SalesOrderID and renames it as "Avg Quantity".
COUNT(OrderQty) OVER (PARTITION BY SalesOrderID) AS "No of Orders": Counts the occurrences of OrderQty for each partition defined by SalesOrderID and renames it as "No of Orders".
MIN(OrderQty) OVER (PARTITION BY SalesOrderID) AS "Min Quantity": Finds the minimum value of OrderQty for each partition defined by SalesOrderID and renames it as "Min Quantity".
MAX(OrderQty) OVER (PARTITION BY SalesOrderID) AS "Max Quantity": Finds the maximum value of OrderQty for each partition defined by SalesOrderID and renames it as "Max Quantity".
The FROM clause specifies the SalesOrderDetail table from which the data is retrieved.
The WHERE clause filters the rows to include only those where the SalesOrderID is either 43659 or 43664.

Sample Output:

salesorderid|productid|orderqty|Total Quantity|Avg Quantity      |No of Orders|Min Quantity|Max Quantity|
------------+---------+--------+--------------+------------------+------------+------------+------------+
       43659|      776|       1|            26|2.1666666666666667|          12|           1|           6|
       43659|      777|       3|            26|2.1666666666666667|          12|           1|           6|
       43659|      778|       1|            26|2.1666666666666667|          12|           1|           6|
       43659|      771|       1|            26|2.1666666666666667|          12|           1|           6|
       43659|      772|       1|            26|2.1666666666666667|          12|           1|           6|
       43659|      773|       2|            26|2.1666666666666667|          12|           1|           6|
       43659|      774|       1|            26|2.1666666666666667|          12|           1|           6|
       43659|      714|       3|            26|2.1666666666666667|          12|           1|           6|
       43659|      716|       1|            26|2.1666666666666667|          12|           1|           6|
       43659|      709|       6|            26|2.1666666666666667|          12|           1|           6|
       43659|      712|       2|            26|2.1666666666666667|          12|           1|           6|
       43659|      711|       4|            26|2.1666666666666667|          12|           1|           6|
       43664|      772|       1|            14|1.7500000000000000|           8|           1|           4|
       43664|      775|       4|            14|1.7500000000000000|           8|           1|           4|
...

SQL AdventureWorks Editor:

Practice Online

Contribute your code and comments through Disqus.

Previous: Calculate latest weekly salary of employees.
Next: Find sum, avg, OrderQty for specific orders.