w3resource

AdventureWorks Database: Each salesperson's annual sales orders

SQL Query - AdventureWorks: Exercise-46 with Solution

46. Create a SQL query to display the total number of sales orders each sales representative receives annually. Sort the result set by SalesPersonID and then by the date component of the orderdate in ascending order. Return the year component of the OrderDate, SalesPersonID, and SalesOrderID.

Sample table: Sales.SalesOrderHeader


Click to view Full table

Sample Solution:

WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
AS
(
    SELECT SalesPersonID, SalesOrderID, DATE_PART('year',OrderDate) AS SalesYear
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL
)
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
FROM Sales_CTE
GROUP BY SalesYear, SalesPersonID
ORDER BY SalesPersonID, SalesYear;

Sample Output:

salespersonid|totalsales|salesyear|
-------------+----------+---------+
          274|         4|   2011.0|
          274|        22|   2012.0|
          274|        14|   2013.0|
          274|         8|   2014.0|
          275|        65|   2011.0|
          275|       148|   2012.0|
          275|       175|   2013.0|
          275|        62|   2014.0|
          276|        46|   2011.0|
          276|       151|   2012.0|
          276|       162|   2013.0|
...

Contribute your code and comments through Disqus.

Previous: Using a derived table with multiple values.
Next: Average sales orders per sales agent.

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

How to create a foreign key in SQL Server?

create table question_bank
(
    question_id uniqueidentifier primary key,
    question_exam_id uniqueidentifier not null,
    question_text varchar(1024) not null,
    question_point_value decimal,
    constraint fk_questionbank_exams foreign key (question_exam_id) references exams (exam_id)
);

Database: SQL Server

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