MS SQL - Grouping orders by month and showing the total yearly amount of orders for that year

Question

I need to create a query that will join data from two tables:

[Orders]
[People]

and give the following output:

SalespersonID
FullName
Order Month
Monthly amount of orders
Yearly amount of orders

The trick here is with the Yearly amount of orders. While the monthly amount of orders should be broken down by the month, the yearly amount should be the same for each given month & salesperson.

The first thing that came to my mind is to use OVER (PARTITION BY ORDER BY ).

So, I came up with this query:

SELECT
    ORD.SalespersonPersonID,
    PEO.FullName,
    [Order Month] = MONTH(ORD.OrderDate),
    [Monthly Amount of Orders] = COUNT(ORD.OrderID),
    [Yearly Amount of Orders] = COUNT(ORD.OrderID) OVER (PARTITION BY ORD.SalespersonPersonID ORDER BY MONTH(ORD.OrderDate))
FROM [Sales].[Orders] ORD LEFT JOIN [Application].[People] PEO
ON ORD.SalespersonPersonID = PEO.PersonID
WHERE YEAR(ORD.OrderDate) = '2016'
GROUP BY ORD.SalespersonPersonID, PEO.FullName, MONTH(ORD.OrderDate), ORD.OrderID
ORDER BY PEO.FullName

The problem is because I have to mention ORD.OrderID in the GROUP BY it breaks the monthly aggregation.

I came with a different approach and solved this (Using a CTE):

WITH table1 AS
(
SELECT 
    SalespersonPersonID,
    [Yearly Amount of Orders] = COUNT(OrderID)
FROM [Sales].[Orders]
WHERE YEAR(OrderDate) = '2016'
GROUP BY SalespersonPersonID
)
SELECT
    ORD.SalespersonPersonID,
    PEO.FullName,
    [Order Month] = MONTH(ORD.OrderDate),
    [Monthly Amount of Orders] = COUNT(ORD.OrderID),
    T1.[Yearly Amount of Orders]
FROM [Sales].[Orders] ORD
LEFT JOIN [Application].[People] PEO
ON ORD.SalespersonPersonID = PEO.PersonID
LEFT JOIN table1 AS T1
ON ORD.SalespersonPersonID = T1.SalespersonPersonID
WHERE YEAR(ORD.OrderDate) = '2016'
GROUP BY ORD.SalespersonPersonID, PEO.FullName, MONTH(ORD.OrderDate), T1.[Yearly Amount of Orders]
ORDER BY PEO.FullName

I am still wondering how it is possible to run this using OVER (PARTITION BY ORDER BY ). Feels like it is the more elegant way to solve this.

Thanks,

Michael

user_10676716 · Answer

No need for a cte or the-like. Starting from your original group by query, you would would do:

SUM(COUNT(ORD.OrderID)) OVER (PARTITION BY ORD.SalespersonPersonID 
    as [Yearly Amount of Orders]

Conceptually, the window function operates on the resultset after aggregation has been performed. You can then access the monthly count of each row, and sum the whole year for the sales person.

Notes:

you want the same value on all rows of the same person, so do not use an order by clause in the over() clause
I doubt that OrderID can ever be null; if so, then use COUNT(*) instead of COUNT(ORD.OrderID). It is more efficient since the database does not need to check for nullity

user_1144035 · Answer

I think you want:

SELECT
    ORD.SalespersonPersonID,
    PEO.FullName,
    [Order Month] = MONTH(ORD.OrderDate),
    [Monthly Amount of Orders] = COUNT(ORD.OrderID),
    [Yearly Amount of Orders] = SUM(COUNT(ORD.OrderID)) OVER (PARTITION BY ORD.SalespersonPersonID)
FROM [Sales].[Orders] ORD LEFT JOIN
     [Application].[People] PEO
     ON ORD.SalespersonPersonID = PEO.PersonID
WHERE YEAR(ORD.OrderDate) = '2016'
GROUP BY ORD.SalespersonPersonID, PEO.FullName, MONTH(ORD.OrderDate)
ORDER BY PEO.FullName;

The only change is to the yearly amount of orders.

MS SQL - Grouping orders by month and showing the total yearly amount of orders for that year

2 Answers