Group By extensions II: CUBE

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
01.01.2017 1 Product 1 20
2 Product 2 30
3 Product 3 40
02.01.2017 1 Product 1 30
2 Product 2 40
3 Product 3 50
….
30.04.2017 3 Product 3 330

 

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)
                              );

Result:

BLOG_0020_PIC01_Result_Query1

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,
             PRODUCT_NO,
             PRODUCT_NAME,
             SUM(TURNOVER)
FROM TBL_TEST
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;

Result:

BLOG_0020_PIC02_Result_Query2

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)!

Download

BLOG_0020_Testdata_Examples_zip

Share and Enjoy: Diese Icons verlinken auf Bookmark Dienste bei denen Nutzer neue Inhalte finden und mit anderen teilen können.
  • LinkedIn
  • XING
  • Facebook
  • Google Bookmarks
  • Twitter

Ein Kommentar

  1. […] 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 […]

Hinterlasse eine Antwort

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind markiert *