w3resource

AdventureWorks Database: Find average bonus for salespersons achieved quota above 25000

SQL Query - AdventureWorks: Exercise-92 with Solution

92. From the following table write a query in SQL to find the average bonus for the salespersons who achieved the sales quota above 25000. Return number of salespersons, and average bonus.

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:

-- Counting the number of records and calculating the average bonus
SELECT COUNT(*), AVG(Bonus)  
-- From the SalesPerson table in the Sales schema
FROM Sales.SalesPerson  
-- Filtering records where SalesQuota is greater than 25000
WHERE SalesQuota > 25000;

Explanation:

  • This SQL query counts the number of records and calculates the average bonus for salespersons in the SalesPerson table of the Sales schema.
  • The COUNT(*) function is used to count all rows in the table that satisfy the given condition.
  • The AVG(Bonus) function calculates the average bonus amount for salespersons meeting the condition.
  • The condition WHERE SalesQuota > 25000 filters records where the sales quota is greater than 25000.

Sample Output:

count|avg                  |
-----+---------------------+
   14|3472.1428571428571429|

SQL AdventureWorks Editor:

Practice Online


Contribute your code and comments through Disqus.

Previous: Find the total number of employees.
Next: Return maximum, minimum, average salary and total employees for each department.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://www.w3resource.com/sql-exercises/adventureworks/sql-adventureworks-exercise-92.php