AdventureWorks Database: Round the value of SalesYTD dividing by CommissionPCT

Last update on December 20 2024 11:38:37 (UTC/GMT +8 hours)

81. From the following table write a query in SQL to calculate by dividing the total year-to-date sales (SalesYTD) by the commission percentage (CommissionPCT). Return SalesYTD, CommissionPCT, and the value rounded to the nearest whole number.

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 the yearly sales amount (SalesYTD), commission percentage (CommissionPCT),
-- and computing the total sales by dividing SalesYTD by CommissionPCT, then rounding to the nearest integer
SELECT SalesYTD, CommissionPCT, 
       CAST(ROUND(SalesYTD / CommissionPCT, 0) AS INT) AS Computed  

-- From the Sales schema's SalesPerson table
FROM Sales.SalesPerson   

-- Filtering the results to include only rows where the commission percentage is not equal to zero
WHERE CommissionPCT != 0;

Explanation:

The SQL query retrieves data from the SalesPerson table within the Sales schema.
It selects the yearly sales amount (SalesYTD) and the commission percentage (CommissionPCT).
The computed column 'Computed' is calculated by dividing SalesYTD by CommissionPCT and rounding the result to the nearest integer.
The CAST() function is used to ensure that the computed value is treated as an integer.
The ROUND() function rounds the division result to zero decimal places.
The WHERE clause filters the rows to include only those where the commission percentage is not equal to zero.

Sample Output:

salesytd    |commissionpct|computed |
------------+-------------+---------+
3763178.1787|        0.012|313598182|
4251368.5497|        0.015|283424570|
3189418.3662|        0.015|212627891|
1453719.4653|         0.01|145371947|
 2315185.611|         0.01|231518561|
1352577.1325|         0.01|135257713|
2458535.6169|         0.01|245853562|
2604540.7172|        0.015|173636048|
1573012.9383|        0.012|131084412|
1576562.1966|        0.019| 82976958|
1421810.9242|        0.018| 78989496|
1827066.7118|        0.018|101503706|
4116871.2277|         0.02|205843561|
3121616.3202|        0.016|195101020|

SQL AdventureWorks Editor:

Practice Online

Contribute your code and comments through Disqus.

Previous: Retrieve products that first two digits of listprice 33.
Next: Find individuals whose SalesYTD first digit contains a 2.