w3resource

AdventureWorks Database: Average sales orders per sales agent

SQL Query - AdventureWorks: Exercise-47 with Solution

47. From the following table write a query in SQL to find the average number of sales orders for all the years of the sales representatives.

Sample table: Sales.SalesOrderHeader


Click to view Full table

Sample Solution:

WITH Sales_CTE (SalesPersonID, NumberOfOrders)
AS
(
    SELECT SalesPersonID, COUNT(*)
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL
    GROUP BY SalesPersonID
)
SELECT AVG(NumberOfOrders) AS "Average Sales Per Person"
FROM Sales_CTE;

Sample Output:

Average Sales Per Person|
------------------------+
    223.8823529411764706|

SQL AdventureWorks Editor:

Practice Online


Contribute your code and comments through Disqus.

Previous: Each salesperson's annual sales orders.
Next: Retrieve records contain green_ in a field.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Share this Tutorial / Exercise on : Facebook and Twitter

SQL: Tips of the Day

T-SQL Cast versus Convert?

CONVERT is SQL Server specific, CAST is ANSI.

CONVERT is more flexible in that you can format dates etc. Other than that, they are pretty much the same. If you don't care about the extended features, use CAST.

Ref : https://bit.ly/3VKT5M8