AdventureWorks Database: Find the TotalSalesYTD of each SalesQuota

Last update on October 11 2025 12:42:23 (UTC/GMT +8 hours)

99. From the following table write a query in SQL to find the TotalSalesYTD of each SalesQuota. Show the summary of the TotalSalesYTD amounts for all SalesQuota groups. Return SalesQuota and TotalSalesYTD.

Sample table: Sales.SalesPerson

businessentityid|territoryid|salesquota|bonus|commissionpct|salesytd    |saleslastyear|rowguid                             |modifieddate           |
----------------+-----------+----------+-----+-------------+------------+-------------+------------------------------------+-----------------------+
             274|           |          |    0|            0| 559697.5639|            0|48754992-9ee0-4c0e-8c94-9451604e3e02|2010-12-28 00:00:00.000|
             275|          2|    300000| 4100|        0.012|3763178.1787| 1750406.4785|1e0a7274-3064-4f58-88ee-4c6586c87169|2011-05-24 00:00:00.000|
             276|          4|    250000| 2000|        0.015|4251368.5497| 1439156.0291|4dd9eee4-8e81-4f8c-af97-683394c1f7c0|2011-05-24 00:00:00.000|
             277|          3|    250000| 2500|        0.015|3189418.3662| 1997186.2037|39012928-bfec-4242-874d-423162c3f567|2011-05-24 00:00:00.000|
             278|          6|    250000|  500|         0.01|1453719.4653| 1620276.8966|7a0ae1ab-b283-40f9-91d1-167abf06d720|2011-05-24 00:00:00.000|
             279|          5|    300000| 6700|         0.01| 2315185.611| 1849640.9418|52a5179d-3239-4157-ae29-17e868296dc0|2011-05-24 00:00:00.000|
             280|          1|    250000| 5000|         0.01|1352577.1325|  1927059.178|be941a4a-fb50-4947-bda4-bb8972365b08|2011-05-24 00:00:00.000|
             281|          4|    250000| 3550|         0.01|2458535.6169| 2073505.9999|35326ddb-7278-4fef-b3ba-ea137b69094e|2011-05-24 00:00:00.000|
             282|          6|    250000| 5000|        0.015|2604540.7172| 2038234.6549|31fd7fc1-dc84-4f05-b9a0-762519eacacc|2011-05-24 00:00:00.000|
             283|          1|    250000| 3500|        0.012|1573012.9383| 1371635.3158|6bac15b2-8ffb-45a9-b6d5-040e16c2073f|2011-05-24 00:00:00.000|
             284|          1|    300000| 3900|        0.019|1576562.1966|            0|ac94ec04-a2dc-43e3-8654-dd0c546abc17|2012-09-23 00:00:00.000|
             285|           |          |    0|            0| 172524.4512|            0|cfdbef27-b1f7-4a56-a878-0221c73bae67|2013-03-07 00:00:00.000|
             286|          9|    250000| 5650|        0.018|1421810.9242| 2278548.9776|9b968777-75dc-45bd-a8df-9cdaa72839e1|2013-05-23 00:00:00.000|
             287|           |          |    0|            0|  519905.932|            0|1dd1f689-df74-4149-8600-59555eef154b|2012-04-09 00:00:00.000|
             288|          8|    250000|   75|        0.018|1827066.7118| 1307949.7917|224bb25a-62e3-493e-acaf-4f8f5c72396a|2013-05-23 00:00:00.000|
             289|         10|    250000| 5150|         0.02|4116871.2277| 1635823.3967|25f6838d-9db4-4833-9ddc-7a24283af1ba|2012-05-23 00:00:00.000|
             290|          7|    250000|  985|        0.016|3121616.3202| 2396539.7601|f509e3d4-76c8-42aa-b353-90b7b8db08de|2012-05-23 00:00:00.000|

Click to view Full table

Sample Solution:

-- Selecting SalesQuota, the sum of SalesYTD, and the grouping indicator
SELECT SalesQuota, SUM(SalesYTD) as "TotalSalesYTD" , 
-- Calculating the grouping indicator for SalesQuota
GROUPING(SalesQuota) as "Grouping" 
-- From the SalesPerson table in the Sales schema
FROM Sales.SalesPerson  
-- Grouping the results and applying rollup aggregation to SalesQuota
GROUP BY rollup(SalesQuota);

Explanation:

This SQL query calculates the total SalesYTD for each SalesQuota and includes a grouping indicator.
The SELECT clause specifies the columns to be retrieved: SalesQuota, the sum of SalesYTD, and the grouping indicator.
The FROM clause indicates the source table, which is SalesPerson in the Sales schema.
The GROUP BY clause groups the results and applies the ROLLUP aggregation to the SalesQuota column. This generates subtotal rows for each distinct value of SalesQuota and a grand total row.
The GROUPING() function is used to determine whether a row represents a subtotal or grand total. It returns 1 for subtotal rows and 0 for non-subtotal rows.

Sample Output:

salesquota|TotalSalesYTD|Grouping|
----------+-------------+--------+
          |36277591.9034|       1|
          | 1252127.9471|       0|
    300000| 7654925.9863|       0|
    250000|27370537.9700|       0|

Go to:

PREV : Find total ListPrice and StandardCost of products of distinct color.
NEXT : Find the sum of the ListPrice and StandardCost for each color.

SQL AdventureWorks Editor:

Practice Online

Contribute your code and comments through Disqus.