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
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:

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.



Follow us on Facebook and Twitter for latest update.