again something on the GROUP BY extensions. Today we will deal with GROUPING SETS. Probably the most flexible and easiest way to create multiple GROUP BYs within one query. In part 1 of this series we discovered the ROLLUP clause, which let’s us automatically calculate totals along some kind of dimension, which is defined by the grouping columns. Part 2 described the CUBE clause which just simply calculate all possible sums based on the grouping columns.
GROUPING SETS let you exactly specify the totals you want to see in your query. If you just want a report total and some monthly totals then you can just define that and you get it. If you need all the totals for all levels of a dimension a ROLLUP might be easier, but in most cases GROUPING SETS will probably be.
Now let’s get to coding. The examples are based on the test data of part 2, i.e. the table looks something like this:
|Day||Product No||Product Name||Turnover|
In my first example I just want a monthly toal and a report total. With GROUPING SETS it looks like this:
|SELECT to_char(DAY,’MM/YYYY’) AS MONTH, SUM(TURNOVER) AS TURNOVER FROM TBL_TEST GROUP BY GROUPING SETS( (to_char(DAY,’MM/YYYY’)), () );|
As you can see in this example you just put all the different GROUP BYs in different sets of brackets. All the columns that belong to a specific GROUP BY are put to one pair of brackets. The different GROUP BYs are then separated by comma. If a GROUP BY only consist of one column you could theoretically leave out the brackets. If you want a report total you leave the brackets empty.Now another example with a monthly total, a report total and a total per month and product:
|SELECT to_char(DAY,’MM/YYYY’) AS MONTH, PRODUCT_NO, PRODUCT_NAME, SUM(TURNOVER) AS TURNOVER FROM TBL_TEST GROUP BY GROUPING SETS( to_char(DAY,’MM/YYYY’), (), (to_char(DAY,’MM/YYYY’), PRODUCT_NO, PRODUCT_NAME) ) ORDER BY MONTH, PRODUCT_NO NULLS LAST|
In this example we have calculated three sums as you can see directly in the GROUPING SETS section of the SQL. This example could have been produced also with a ROLLUP if you change the clauses and leave out the first two rows of the GROUPING SETS section. As you can see often there are several ways to reach the target. For larger reports and if you need a lot of sums maybe ROLLUP makes it easier for you and the database. But sometimes you only need a few specific totals out of a lot of different combinations then maybe GROUPING SETS are easier to use.
The examples used in this article can be found at the very end of this article as zip-file. I will continue this series soon and we will then deal with some useful functions you can use if you are working with the GROUP BY extensions.
If you are interested in this or other advanced Cognos/SQL topics you can also attend training on my corporate homepage. At the moment my open courses are only available in German. Inhouse training and online courses are also available in English language. So check out my open trainings (German) or my online courses (English)!