Problem
As you may know, Microsoft has removed the COMPUTE BY clause in the SQL Server SELECT statement, starting with SQL Server 2012. The ROLL UP clause, which is supposed to be a replacement for COMPUTE BY, does not give a similar result. In the article, Using SQL Server ROLL UP to Get Similar Results of COMPUTE BY, I presented how we can replace the COMPUTE BY clause by using the ROLL UP clause instead.
If we export data to Excel, we can do exactly what COMPUTE BY does. For this tutorial, I will use SQL Server 2019, Visual Studio 2019 and Microsoft Excel 2013. Some of the C# code will not work if you don’t use the latest .NET (minimum 4.8). I will also use the AdventureWorks database.
Solution
The COMPUTE BY clause has some disadvantages. One disadvantage is that it generates a lot of data sets, two data sets for each group of rows for which it does a computation. For example, in Using SQL Server ROLL UP to Get Similar Results of COMPUTE BY we have 6 groups of data rows, and a total of 12 data sets.
Another disadvantage of COMPUTE BY is speed. For the AdventureWorks database I used in this article, the initial example I wanted to use had around 121,000 rows. When I used COMPUTE BY clause in SQL Server Management Studio (SSMS) I had to stop the process after about one hour. Using the same SELECT without COMPUTE BY took around 4 seconds. Having so many rows exported to Excel will create a very large Excel file, so I decided to restrict the selected data, such that there will be only around 1,500 rows. The purpose of this article is to show how we can get exactly the same result that the COMPUTE BY clause gives.
To illustrate how this works, I will use the following SELECT statement:
SELECT
SalesOrderID, Quantity, UnitPrice, OrderTotal, [Name]
FROM
(
SELECT
SalesOrderID,
productid as productid,
OrderQty as Quantity,
UnitPrice as UnitPrice,
OrderQty*UnitPrice as OrderTotal
FROM Sales.SalesOrderDetail
WHERE Year(ModifiedDate) = 2014 and MONTH(ModifiedDate) <= 2 and OrderQty > 1
) sales
JOIN
(
SELECT
productid,
name
FROM [Production].[Product]
) product
ON sales.productid = product.ProductID
ORDER BY SalesOrderID, name
--COMPUTE sum(ordertotal) BY salesorderid
I commented the COMPUTE BY clause to illustrate what I want to get: for each order with a minimum of 2 items, issued in the first two months of 2014 we compute its total.
The method used to simulate the behavior of COMPUTE BY is straight forward:
- Read the first row and keep the salesorderid as the first element in the sum of first group.
- For each row starting with the second, compare the current salesorderid with the previous one. If it is the same, increase the order total for the current group with the total of the current row.
- If the salesorderid of the current row is different than the previous one, start a new group.
This is illustrated in the following code:
if(dr.HasRows)
{
for(int j = 0; j < dr.FieldCount; ++j)
{
xlsWorksheet.Cells[i, j + 1] = dr.GetName(j);
}
++i;
// First read
dr.Read();
prevSalesOrderId = dr.GetInt32(0);
currentSalesOrderId = prevSalesOrderId;
orderTotal = (double)dr.GetDecimal(3);
for(int j = 1; j <= dr.FieldCount; ++j)
xlsWorksheet.Cells[i, j] = dr.GetValue(j - 1);
++i;
}
while(dr.Read())
{
currentSalesOrderId = dr.GetInt32(0);
if(currentSalesOrderId == prevSalesOrderId)
{
orderTotal += (double)dr.GetDecimal(3);
}
else
{
// We have the first sum
xlsWorksheet.Cells[i, 4] = orderTotal;
orderTotal = (double)dr.GetDecimal(3);
prevSalesOrderId = currentSalesOrderId;
++i;
}
for(int j = 1; j <= dr.FieldCount; ++j)
xlsWorksheet.Cells[i, j] = dr.GetValue(j - 1);
++i;
}
There are several ways of doing these computations, but in my opinion this approach is simple and doesn’t involve a lot of programming effort. We can also use more complex situations, where we can use several mathematical functions (in this example we only use SUM).
Next Steps
