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

# 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|
...
```

## Practice Online

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.

﻿