Avoid duplicate calculations in SELECT statements

SELECT  oh.CustomerID,
        SUM(oh.TaxAmt + oh.Freight) AS Extra,
        MAX(SUBSTRING(c.AccountNumber, 6,2)) AS Kontoklass
FROM Sales.SalesOrderHeader oh
INNER JOIN Sales.Customer c ON c.CustomerID = oh.CustomerID
GROUP BY oh.CustomerID
HAVING SUM(oh.TaxAmt + oh.Freight) > 10000
    AND MAX(SUBSTRING(c.AccountNumber, 6,2)) = '30'

In this simple exeample you need to have the calculations in both the SELECT and GROUP BY parts of the query, because the SELECT clause is executed after the GROUP BY claus. Wouldn’t it be nice if we could have our formulas in just one place? We can! By defining the formulas in the FROM clause their aliases can be used in all other places. The trick is to use the APPLY clause and a derived table. This table can use columns from all previous tables in the statement as parameters and return one row with custom calculations. So, lets rewrite the query.

SELECT  oh.CustomerID,
        SUM(Extra) AS Extra,
        MAX(Kontoklass) AS Kontoklass
FROM Sales.SalesOrderHeader oh
INNER JOIN Sales.Customer c ON c.CustomerID = oh.CustomerID
CROSS APPLY (SELECT /********* Formulas *********/
                  oh.TaxAmt + oh.Freight            AS Extra,
                  SUBSTRING(c.AccountNumber, 6,2)   AS Kontoklass
                  ) AS t
GROUP BY oh.CustomerID
HAVING SUM(Extra) > 10000
   AND MAX(Kontoklass) = '30'

Nice! This way we don’t need to repeat the formulas with copy and paste many times, and most important – it’s much easier to maintain the code in the future. But wait, what about performance? Don’t worry. In most cases (if not all) SQL Server will use the same execution plan.

If you have any SQL Server related problem – don’t hesitate to contact any of SQL Service consultants.