Summarizing Data – Part 3

The SELECT reserved word is part of the Data Manipulation Language (DML) defined by Codd and is used to query data from the database. Because of its popularity, there are many clauses and functions that can be used to construct a query to retrieve data.

I am continuing our exploration by reviewing SELECT queries that aggregate data. Data aggregation is the process of converting many records into a few records with special meaning. I will be using the Adventure Works 2012 sample database supplied by Microsoft during this talk.

I will be exploring the COMPUTE operator which is gone from SQL Server 2012. There are two things I want the reader to learn from this article: always check the engine release notes discontinued syntax and learn how to replace your organizations functionality with a substitute operator.

The following example is copied directly from the MSDN page for the COMPUTE operator.

Please note that there is 451 resulting rows with the grand total as a second result set (MARS). Enclosure OUT-2008R2 is the textual output from the query.

Looking closely at the discontinued database engine functionality article, it suggests to replace the COMPUTE operator with the ROLLUP operator. The code below rewrites the query to be compatible with the 2012 database engine.

Since the original query did not have sub-totals at the detail level, I captured the results as a derived table. I had to filter the derived table to get the same rows. I used the same technique in the previous blog to handle grouping levels and missing data.

It is interesting to note that there are 451 resulting rows in one result set. Enclosure OUT-2012 is the textual output from the query.

Again, I want to stress that both ANSI SQL and Microsoft TSQL are living works in progress. Always check the release notes for each database engine version to find out what syntax has been added and what has been dropped.

Related posts

Leave a Comment