This is the second part of the series about GROUP BY extensions. In the first part of this series we discovered the ROLLUP statement which lets you easily calculate totals on different levels on granularity without using several queries. ROLLUP just calculates the totals along an artificial dimension which can be defined through expressions for each level.
Beside ROLLUP there is another extension which is called CUBE. CUBE calculates all possible totals for the given expressions. If you have Month and Product in your query and using CUBE then there calculated for totals: Month, Product, Month and Product, Total Query.
Let’s think of the following table with test data:
|Day||Product No||Product Name||Turnover|
The first example creates a cube for day and product (no + name):
|SELECT to_char(DAY,’MM/YYYY‘) AS MONTH, PRODUCT_NO, PRODUCT_NAME, SUM(TURNOVER) FROM TBL_TEST GROUP BY CUBE ( to_char(DAY,’MM/YYYY‘), (PRODUCT_NO, PRODUCT_NAME) );|
This query generates 20 rows. The first 12 rows are the most detailed totals, i.e. a total for month and product. Line 13 – 15 are the totals per product, line 16 – 19 are the totals per month and line 20 is the query total.
To get this result you only have to use the CUBE statement instead of ROLLUP. CUBE then generates all possible sums. If you want two columns to be treated as one (e.g. product no and product name) then you use extra brackets for it ( take a look at the example). Of course you could add more expressions to the CUBE but this always leads to a lot more sums. If you have three expressions in the CUBE then you’ll get 2³=8 totals.
That leads sometimes to the question what to do if you need only specific totals but not all. You could use a CUBE then and use the HAVING to filter out the not needed totals based on values in the grouping columns.
The second example shows how to do that:
SELECT to_char(DAY,’MM/YYYY‘) AS MONTH,
GROUP BY CUBE ( to_char(DAY,’MM/YYYY‘),
(PRODUCT_NO, PRODUCT_NAME)) HAVING PRODUCT_NO IS NOT NULL AND to_char(DAY, ‚MM/YYYY‘) IS NULL OR PRODUCT_NO IS NULL AND to_char(DAY, ‚MM/YYYY‘) IS NOT NULL;
In this SQL I have added the two lines for the HAVING clause. With these two lines I tell the database what sums out of all possible sums I am really interested in. In order to do that I just check that either the month is null and product not or month is not null but product is. Only in these two cases I want the line to be displayed.
Like the ROLLUP CUBE is available on Oracle, MS SQL Server, DB2, Postgres, etc., but not on MySQL. I hope you enjoyed today’s lesson again. 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 the so called GROUPING SETS which let you define sums and totals in a very detailed way.
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)!