Posts Tagged ‘easy sums rollup’

Easy SQL totals and other Aggregates with rollup and cube

March 31, 2011 2 comments

David Cooksey on easy sums with SQL rollup

March 31, 2011 | David Cooksey

Easy SQL totals and other Aggregates with Rollup and Cube

SQL 2008’s introduction of the rollup and cube keywords allows for easy subtotals and other aggregates by your selected groups. To see how simple it is to set up your automatic subtotals, let’s look at an example. For those who want to skip right to SQL, see the script at the bottom.

Imagine you have a table that stores sales information. This table holds the customer name and the products that were purchased, along with the salesman who got credit for the sale and the purchase date. The precise schema for the table follows.

By joining this table to its related lookup tables we see that it has the following data:

If we wanted to write a query to give us totals by customer name, it would look something like the following.

By adding WITH ROLLUP to the query we get an automatic grand total.

The rollup keyword generates aggregate summaries based on the order of the columns in the group by clause. If grouping by customer name, salesman name, and product name—in that order—summary rows will be generated for all products by salesman and customer, and all salesmen and products by customer. However, there will be no summary row for all customers by either salesman name or product name.

Now let’s try something a little more complex. Let’s get totals and averages by customer and salesman.

The cube keywords used here generate summary rows for all combinations of columns in the group by clause. In this result set we have aggregates grouped by customers and aggregates grouped by sales. These summary rows have null in one or more of their columns. The grouping keyword tells us whether a particular row in the result set contains aggregate information for a given column. The grand total row is marked as a grouping row for both sales and customers. The grouping keyword may be necessary to distinguish summary rows from rows that legitimately contain null.

Rollup and cube provide grouped aggregate summaries with very little effort. I highly recommend them for anyone who has to do a lot of SQL report writing!

Download the .sql file

David Cooksey is a Senior .NET developer at Thycotic Software, an agile software services and product development company based in Washington DC. Secret Server is our flagship password management software product.