AdventureWorks Database: Calculate running totals and subtotals

SQL Query - AdventureWorks: Exercise-12 with Solution

12. From the following table write a query in SQL to find the sum of subtotal column. Group the sum on distinct salespersonid and customerid. Rolls up the results into subtotal and running total. Return salespersonid, customerid and sum of subtotal column i.e. sum_subtotal.

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

Click to view Full table

Sample Solution:

-- Selecting columns for salesperson ID, customer ID, and the sum of subtotal sales
SELECT salespersonid, customerid, sum(subtotal) AS sum_subtotal
-- Retrieving data from the 'salesorderheader' table with an alias 's'
FROM sales.salesorderheader s 
-- Grouping the results by the ROLLUP function applied to salesperson ID and customer ID
GROUP BY ROLLUP (salespersonid, customerid);

Explanation:

• The SELECT statement retrieves columns for salesperson ID, customer ID, and the sum of subtotal sales.
• FROM sales.salesorderheader s specifies the table 'salesorderheader' from which the data will be retrieved, with 's' as an alias for the table.
• The GROUP BY clause groups the results based on the ROLLUP function applied to the salesperson ID and customer ID columns.
• The ROLLUP function is used for creating subtotals for each level of aggregation, including grand totals. It generates groupings in the result set that represent higher-level summaries.
• In this case, the ROLLUP function is applied to both salesperson ID and customer ID, which means it will produce subtotals for each combination of salesperson and customer, as well as the grand totals.

Sample Output:

salespersonid|customerid|sum_subtotal  |
-------------+----------+--------------+
          274|     29491|    33406.7043|
          274|     29493|      2146.962|
          274|     29514|     3405.1668|
          274|     29523|    34349.2656|
          274|     29576|        53.994|
          274|     29579|      35331.66|
          274|     29604|       647.994|
          274|     29605|    29482.0603|
          274|     29616|   138046.3212|
          274|     29617|   198993.3507|
          274|     29623|      1946.022|
          274|     29650|        83.988|
          274|     29666|    15842.6141|
          274|     29669|     3962.2441|
          274|     29671|     11802.564|
          274|     29675|       4254.45|
		  ...

SQL AdventureWorks Editor:

Practice Online

Contribute your code and comments through Disqus.

Previous: Persons whose last name begins with 'L'.
Next: GROUP BY CUBE operation on locationid and shelf.