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.

salesorderid|revisionnumber|orderdate              |duedate                |shipdate               |status|onlineorderflag|purchaseordernumber|accountnumber |customerid|salespersonid|territoryid|billtoaddressid|shiptoaddressid|shipmethodid|creditcardid|creditcardapprovalcode|currencyrateid|subtotal   |taxamt    |freight  |totaldue   |comment|rowguid                             |modifieddate           |
------------+--------------+-----------------------+-----------------------+-----------------------+------+---------------+-------------------+--------------+----------+-------------+-----------+---------------+---------------+------------+------------+----------------------+--------------+-----------+----------+---------+-----------+-------+------------------------------------+-----------------------+
       43659|             8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000|     5|false          |PO522145787        |10-4020-000676|     29825|          279|          5|            985|            985|           5|       16281|105041Vi84182         |              | 20565.6206| 1971.5149| 616.0984| 23153.2339|       |79b65321-39ca-4115-9cba-8fe0903e12e6|2011-06-07 00:00:00.000|
       43660|             8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000|     5|false          |PO18850127500      |10-4020-000117|     29672|          279|          5|            921|            921|           5|        5618|115213Vi29411         |              |  1294.2529|  124.2483|  38.8276|  1457.3288|       |738dc42d-d03b-48a1-9822-f95a67ea7389|2011-06-07 00:00:00.000|
       43661|             8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000|     5|false          |PO18473189620      |10-4020-000442|     29734|          282|          6|            517|            517|           5|        1346|85274Vi6854           |             4| 32726.4786| 3153.7696|  985.553| 36865.8012|       |d91b9131-18a4-4a11-bc3a-90b6f53e9d74|2011-06-07 00:00:00.000|
       43662|             8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000|     5|false          |PO18444174044      |10-4020-000227|     29994|          282|          6|            482|            482|           5|       10456|125295Vi53935         |             4| 28832.5289| 2775.1646| 867.2389| 32474.9324|       |4a1ecfc0-cc3a-4740-b028-1c50bb48711c|2011-06-07 00:00:00.000|
       43663|             8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000|     5|false          |PO18009186470      |10-4020-000510|     29565|          276|          4|           1073|           1073|           5|        4322|45303Vi22691          |              |   419.4589|   40.2681|  12.5838|   472.3108|       |9b1e7a40-6ae0-4ad3-811c-a64951857c4b|2011-06-07 00:00:00.000|
       43664|             8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000|     5|false          |PO16617121983      |10-4020-000397|     29898|          280|          1|            876|            876|           5|         806|95555Vi4081           |              | 24432.6088| 2344.9921|   732.81| 27510.4109|       |22a8a5da-8c22-42ad-9241-839489b6ef0d|2011-06-07 00:00:00.000|
       43665|             8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000|     5|false          |PO16588191572      |10-4020-000146|     29580|          283|          1|            849|            849|           5|       15232|35568Vi78804          |              | 14352.7713| 1375.9427| 429.9821| 16158.6961|       |5602c304-853c-43d7-9e79-76e320d476cf|2011-06-07 00:00:00.000|
	   -- more --

Click to view Full table

Sample Solution:

-- Common Table Expression (CTE) named Sales_CTE
WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
AS
(
    -- Selecting SalesPersonID, SalesOrderID, and extracting year from OrderDate as SalesYear
    SELECT SalesPersonID, SalesOrderID, DATE_PART('year',OrderDate) AS SalesYear
    FROM Sales.SalesOrderHeader
    -- Filtering rows where SalesPersonID is not NULL
    WHERE SalesPersonID IS NOT NULL
)
-- Main query selecting from the Sales_CTE
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
FROM Sales_CTE
-- Grouping the results by SalesYear and SalesPersonID
GROUP BY SalesYear, SalesPersonID
-- Ordering the results by SalesPersonID and SalesYear
ORDER BY SalesPersonID, SalesYear;

Explanation:

• The SQL query uses a Common Table Expression (CTE) named Sales_CTE to calculate sales data.
• The CTE retrieves data from the SalesOrderHeader table in the Sales schema.
• It selects SalesPersonID, SalesOrderID, and extracts the year from the OrderDate column as SalesYear.
• Rows are filtered to include only those where SalesPersonID is not NULL.
• The main query then selects from the Sales_CTE CTE.
• It calculates the total number of sales (TotalSales) for each SalesPersonID and SalesYear combination using the COUNT function.
• Results are grouped by SalesYear and SalesPersonID.

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.