w3resource

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|

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.

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

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