w3resource

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

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


Click to view Full table

Sample Solution:

SELECT SalesOrderID, ProductID, OrderQty
    ,SUM(OrderQty) OVER win AS "Total Quantity"
    ,AVG(OrderQty) OVER win AS "Avg Quantity"
    ,COUNT(OrderQty) OVER win AS "No of Orders"
    ,MIN(OrderQty) OVER win AS "Min Quantity"
    ,MAX(OrderQty) OVER win AS "Max Quantity"
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN(43659,43664)
WINDOW win AS (PARTITION BY SalesOrderID);

Or

SELECT SalesOrderID, ProductID, OrderQty
    ,SUM(OrderQty) OVER (PARTITION BY SalesOrderID) AS "Total Quantity"
    ,AVG(OrderQty) OVER (PARTITION BY SalesOrderID) AS "Avg Quantity"
    ,COUNT(OrderQty) OVER (PARTITION BY SalesOrderID) AS "No of Orders"
    ,MIN(OrderQty) OVER (PARTITION BY SalesOrderID) AS "Min Quantity"
    ,MAX(OrderQty) OVER (PARTITION BY SalesOrderID) AS "Max Quantity"
    FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN(43659,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.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Share this Tutorial / Exercise on : Facebook and Twitter

SQL: Tips of the Day

T-SQL Cast versus Convert?

CONVERT is SQL Server specific, CAST is ANSI.

CONVERT is more flexible in that you can format dates etc. Other than that, they are pretty much the same. If you don't care about the extended features, use CAST.

Ref : https://bit.ly/3VKT5M8