AdventureWorks Database: Find the average and sum of the subtotal
8. From the following table write a query in SQL to find the average and the sum of the subtotal for every customer. Return customerid, average and sum of the subtotal. Grouped the result on customerid and salespersonid. Sort the result on customerid column in descending order.
Sample table: sales.salesorderheader
salesorderid|revisionnumber|orderdate |duedate |shipdate |status|onlineorderflag|purchaseordernumber|accountnumber |customerid|salespersonid|territoryid|billtoaddressid|shiptoaddressid|shipmethodid|creditcardid|creditcardapprovalcode|currencyrateid|subtotal |taxamt |freight |totaldue |comment|rowguid |modifieddate |
------------+--------------+-----------------------+-----------------------+-----------------------+------+---------------+-------------------+--------------+----------+-------------+-----------+---------------+---------------+------------+------------+----------------------+--------------+-----------+----------+---------+-----------+-------+------------------------------------+-----------------------+
43659| 8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000| 5|false |PO522145787 |10-4020-000676| 29825| 279| 5| 985| 985| 5| 16281|105041Vi84182 | | 20565.6206| 1971.5149| 616.0984| 23153.2339| |79b65321-39ca-4115-9cba-8fe0903e12e6|2011-06-07 00:00:00.000|
43660| 8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000| 5|false |PO18850127500 |10-4020-000117| 29672| 279| 5| 921| 921| 5| 5618|115213Vi29411 | | 1294.2529| 124.2483| 38.8276| 1457.3288| |738dc42d-d03b-48a1-9822-f95a67ea7389|2011-06-07 00:00:00.000|
43661| 8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000| 5|false |PO18473189620 |10-4020-000442| 29734| 282| 6| 517| 517| 5| 1346|85274Vi6854 | 4| 32726.4786| 3153.7696| 985.553| 36865.8012| |d91b9131-18a4-4a11-bc3a-90b6f53e9d74|2011-06-07 00:00:00.000|
43662| 8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000| 5|false |PO18444174044 |10-4020-000227| 29994| 282| 6| 482| 482| 5| 10456|125295Vi53935 | 4| 28832.5289| 2775.1646| 867.2389| 32474.9324| |4a1ecfc0-cc3a-4740-b028-1c50bb48711c|2011-06-07 00:00:00.000|
43663| 8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000| 5|false |PO18009186470 |10-4020-000510| 29565| 276| 4| 1073| 1073| 5| 4322|45303Vi22691 | | 419.4589| 40.2681| 12.5838| 472.3108| |9b1e7a40-6ae0-4ad3-811c-a64951857c4b|2011-06-07 00:00:00.000|
43664| 8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000| 5|false |PO16617121983 |10-4020-000397| 29898| 280| 1| 876| 876| 5| 806|95555Vi4081 | | 24432.6088| 2344.9921| 732.81| 27510.4109| |22a8a5da-8c22-42ad-9241-839489b6ef0d|2011-06-07 00:00:00.000|
43665| 8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000| 5|false |PO16588191572 |10-4020-000146| 29580| 283| 1| 849| 849| 5| 15232|35568Vi78804 | | 14352.7713| 1375.9427| 429.9821| 16158.6961| |5602c304-853c-43d7-9e79-76e320d476cf|2011-06-07 00:00:00.000|
-- more --
Sample Solution:
-- Selecting the customer ID, salesperson ID, average subtotal, and total sum of subtotal for each combination of customer and salesperson
SELECT customerid, salespersonid, AVG(subtotal) AS avg_subtotal,
SUM(subtotal) AS sum_subtotal
-- Specifying the table from which the data will be retrieved
FROM sales.salesorderheader
-- Grouping the results by customer ID and salesperson ID
GROUP BY customerid, salespersonid
-- Ordering the result set by customer ID in descending order
ORDER BY customerid DESC;
Explanation:
- The SELECT customerid, salespersonid, AVG(subtotal) AS avg_subtotal, SUM(subtotal) AS sum_subtotal statement retrieves the customer ID, salesperson ID, average subtotal, and total sum of subtotal for each combination of customer and salesperson.
- FROM sales.salesorderheader specifies the table from which the data will be retrieved. 'sales' is the schema name and 'salesorderheader' is the table name.
- The GROUP BY customerid, salespersonid clause groups the results by both customer ID and salesperson ID, allowing the calculation of aggregate functions such as AVG() and SUM() for each group.
- The ORDER BY customerid DESC clause orders the result set by customer ID in descending order.
Sample Output:
customerid|salespersonid|avg_subtotal |sum_subtotal|
----------+-------------+----------------------+------------+
30118| 275| 34638.152183333333| 207828.9131|
30118| 277| 35369.828450000000| 70739.6569|
30117| 275| 77171.792833333333| 463030.7570|
30117| 277| 58954.136550000000| 353724.8193|
30116| 276| 46778.550275000000| 187114.2011|
30115| 289| 1114.6949250000000000| 8917.5594|
30114| 290| 1456.6238875000000000| 11652.9911|
30113| 282| 34148.236350000000| 273185.8908|
30112| 280| 93591.621700000000| 93591.6217|
30112| 284| 54909.378542857143| 384365.6498|
30111| 277| 69131.600775000000| 276526.4031|
30110| 276| 406.3188750000000000| 1625.2755|
30109| 275| 59857.260500000000| 119714.521|
30109| 277| 36114.523283333333| 216687.1397|
30108| 289| 36150.645400000000| 144602.5816|
...
SQL AdventureWorks Editor:
Practice Online
Contribute your code and comments through Disqus.
Previous: Calculate the total freight paid by each customer.
Next: Find total quantity of each product in specific shelves.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.