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

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

Sample table: Sales.SalesOrderDetail

Click to view Full table

Sample Solution:

``````SELECT SalesOrderID AS OrderNumber, ProductID,
OrderQty AS Quantity,
SUM(OrderQty) OVER (ORDER BY SalesOrderID, ProductID) AS Total,
AVG(OrderQty) OVER(PARTITION BY SalesOrderID ORDER BY SalesOrderID, ProductID) AS Avg,
COUNT(OrderQty) OVER(ORDER BY SalesOrderID, ProductID ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING) AS Count
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN(43659,43664) and CAST(ProductID AS TEXT) LIKE '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|
```

## Practice Online

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.

﻿

## SQL: Tips of the Day

How to get the top 10 values in PostgreSQL?

```select *
from scores
order by score desc
limit 10
```

Database: PostgreSQL

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