The Problem
We need to obtain a result set to see the sales amount for each company per year. This part is easy, we can use a simply GroupBy() to obtain the sales amount per each company per year. However, we also need a running cumulative amount per company and the total amount of sales per the the entire company, and for this we need to use the new OVER clause syntax. Notice below how the Cumulative Amount is adding the Amount per each year of the company, while the Total Amount.
Total Cumulative Total
CompanyName Period Items Amount Amount Amount
------------------------------------ ------- ------ -------- ----------- -------
Alfreds Futterkiste 1997 6 2022.50 2022.50 4273.00
Alfreds Futterkiste 1998 6 2250.50 4273.00 4273.00
Ana Trujillo Emparedados y helados 1996 2 88.80 88.80 1402.95
Ana Trujillo Emparedados y helados 1997 4 799.75 888.55 1402.95
Ana Trujillo Emparedados y helados 1998 4 514.40 1402.95 1402.95
Antonio Moreno Taquería 1996 1 403.20 403.20 7023.97
Antonio Moreno Taquería 1997 14 5960.77 6363.97 7023.97
Antonio Moreno Taquería 1998 2 660.00 7023.97 7023.97
In fact, we need a nested query to get the entire result set. So we'll attack this query in two steps.
Step 1
Let's create what will become our nested query. We need to obtain the CompanyName, Period, TotalItems, and Amount grouped by CompanyName and the YEAR. We start from the Orders table and join with the Customers table to get the CompanyName and the Order Details table to get the line item information for each order.
OrdersCollection coll = new OrdersQuery("o", out var o)
.InnerJoin<CustomersQuery>("c", out var c).On(c.CustomerID == o.CustomerID)
.InnerJoin<OrderDetailsQuery>("od", out var od).On(od.OrderID == o.OrderID)
.Select
(
// We're going to grab the aliased columns here for re-use in the outer query later
c.CompanyName.As("CompanyName"),
o.OrderDate.DatePart("year").As("Period"),
o.Count().As("TotalItems"),
((1.00M - od.Discount) * od.UnitPrice * od.Quantity).Cast(esCastType.Decimal, 19, 2)
.Sum().Round(2).As("Amount")
)
.GroupBy(c.CompanyName, o.OrderDate.DatePart("year"))
.OrderBy(c.CompanyName.Ascending, o.OrderDate.DatePart("year").Ascending)
.ToCollection<OrdersCollection>();The SQL generated by the above query is as follows:
SELECT
c.[CompanyName] AS 'CompanyName',
DATEPART(year, o.[OrderDate]) AS 'Period',
COUNT(*) AS 'TotalItems',
CAST(SUM(ROUND((((1.00 - od.[Discount]) * od.[UnitPrice]) * od.[Quantity]), 2))
AS decimal(19, 2)) AS 'Amount'
FROM
[Orders] o
INNER JOIN [Customers] c ON c.[CustomerID] = o.[CustomerID]
INNER JOIN [Order Details] od ON od.[OrderID] = o.[OrderID]
GROUP BY c.[CompanyName], DATEPART(year, o.[OrderDate])
ORDER BY c.[CompanyName] ASC, DATEPART(year, o.[OrderDate]) ASCThe result set is as follows:
Total
CompanyName Period Items Amount
---------------------------------------- ----------- ----------- -----------
Alfreds Futterkiste 1997 6 2022.50
Alfreds Futterkiste 1998 6 2250.50
Ana Trujillo Emparedados y helados 1996 2 88.80
Ana Trujillo Emparedados y helados 1997 4 799.75
Ana Trujillo Emparedados y helados 1998 4 514.40
Antonio Moreno Taquería 1996 1 403.20
Antonio Moreno Taquería 1997 14 5960.77
Antonio Moreno Taquería 1998 2 660.00
Step 2
Okay, so step one is complete. But now we need to obtain a cumulative running total sales amount for the company as each year progresses and we need the total amount of sales for the entire company. These two values are not "per" year so we are going to nest the query above into yet another query and use the OVER clause syntax to get the two additional values. Let's see how that is done.
Don't let this query overwhelm you. If you could collapse the nested query all you would see is the Select() and the OrderBy() on the nested queries columns. Take a close look at how we obtain the "CumulativeAmount" and "TotalAmount" values. Also notice the use of esAlias and that it's knows what alias to use, in this case "sub".
Our full query.
// We grab these aliases in the nested query via "out" parameters
esAlias aliasCompany = null, aliasPeriod = null, aliasAmount = null, aliasItemCount = null;
OrdersCollection coll = new OrdersQuery("q", out var q)
.From<OrdersQuery>(out var sub, () => // mimic a CTE
{
// Nested Query from Step 1 above
return new OrdersQuery("o", out var o)
.InnerJoin<CustomersQuery>("c", out var c).On(c.CustomerID == o.CustomerID)
.InnerJoin<OrderDetailsQuery>("od", out var od).On(od.OrderID == o.OrderID)
.Select
(
// We're going to grab the aliased columns here for re-use in the outer query later
c.CompanyName.As("CompanyName", out aliasCompany),
o.OrderDate.DatePart("year").As("Period", out aliasPeriod),
o.Count().As("TotalItems", out aliasItemCount),
((1.00M - od.Discount) * od.UnitPrice * od.Quantity).Cast(esCastType.Decimal, 19, 2)
.Sum().Round(2).As("Amount", out aliasAmount)
)
.GroupBy(c.CompanyName, o.OrderDate.DatePart("year"));
}).As("sub")
// Now act on "sub" query columns using the OVER syntax
.Select(
aliasCompany(), aliasPeriod(), aliasAmount(), aliasItemCount(),
q.Over.Sum(aliasAmount()).PartitionBy(aliasCompany()).OrderBy(aliasPeriod().Ascending).Rows
.UnBoundedPreceding.As("CumulativeAmount"),
q.Over.Sum(aliasAmount()).PartitionBy(aliasCompany()).As("TotalAmount")
)
.OrderBy(aliasCompany().Ascending, aliasPeriod().Ascending)
.ToCollection<OrdersCollection>();
if(coll.Count > 0)
{
// we loaded data
}SELECT
sub.[CompanyName],
sub.[Period],
sub.[Amount],
sub.[TotalItems],
-- Notice the OVER() clause usage below
SUM([Amount]) OVER( PARTITION BY [CompanyName] ORDER BY sub.[Period] ASC ROWS UNBOUNDED PRECEDING )
AS 'CumulativeAmount',
SUM([Amount]) OVER( PARTITION BY [CompanyName] ) AS 'TotalAmount'
FROM
(
SELECT
c.[CompanyName] AS 'CompanyName',
DATEPART(year, o.[OrderDate]) AS 'Period',
COUNT(*) AS 'TotalItems',
CAST(SUM(ROUND((((1.00 - od.[Discount]) * od.[UnitPrice]) * od.[Quantity]), 2)) AS decimal(19, 2))
AS 'Amount'
FROM [Orders] o
INNER JOIN [Customers] c ON c.[CustomerID] = o.[CustomerID]
INNER JOIN [Order Details] od ON od.[OrderID] = o.[OrderID]
GROUP BY c.[CompanyName], DATEPART(year, o.[OrderDate])
)
AS sub
ORDER BY sub.[CompanyName] ASC, sub.[Period] ASC
Total Cumulative Total
CompanyName Period Items Amount Amount Amount
------------------------------------ ------- ------ -------- ----------- -------
Alfreds Futterkiste 1997 6 2022.50 2022.50 4273.00
Alfreds Futterkiste 1998 6 2250.50 4273.00 4273.00
Ana Trujillo Emparedados y helados 1996 2 88.80 88.80 1402.95
Ana Trujillo Emparedados y helados 1997 4 799.75 888.55 1402.95
Ana Trujillo Emparedados y helados 1998 4 514.40 1402.95 1402.95
Antonio Moreno Taquería 1996 1 403.20 403.20 7023.97
Antonio Moreno Taquería 1997 14 5960.77 6363.97 7023.97
Antonio Moreno Taquería 1998 2 660.00 7023.97 7023.97
That is some pretty powerful functionality that EntitySpaces is packing. Both the full SQL Over Clause syntax along with the full SQL Window Frame syntax are supported.
