SAP HANA Usage of Grouping

[Former Member]

Hi folks,

I'm new to SAP HANA and need your help with understanding how GROUPING SETS on HANA works.

By executing of this statement

SELECT      COUNTRY, LANGU, "SHORT TEXT",
GROUPING(COUNTRY), GROUPING(LANGU), GROUPING("SHORT TEXT"),
GROUPING_ID(COUNTRY, LANGU, "SHORT TEXT")
FROM COUNTRIES
GROUP BY GROUPING SETS ((COUNTRY, LANGU), (LANGU, "SHORT TEXT"))

on this raw table

COUNTRY	LANGU	SHORT TEXT
DE	D	Deutschland
DE	E	Germany
US	D	USA
US	E	USA

I'm getting these results:

COUNTRY	LANGU	SHORT TEXT	GROUPING(COUNTRY)	GROUPING(LANGU)	GROUPING(SHORT TEXT)	GROUPING_ID
DE	D	?	0	0	1	1
US	D	?	0	0	1	1
DE	E	?	0	0	1	1
US	E	?	0	0	1	1
?	D	Deutschland	1	0	0	4
?	E	Germany	1	0	0	4
?	D	USA	1	0	0	4
?	E	USA	1	0	0	4

Two questions raised here:

• Why is Grouping(...) returning 1 when attribute is NULL and 0 when grouped? Has it not to be reversely?
• And what about Grouping_ID, I have expected to see 1 and 2, but not 4... What are respective rules here?

Regards,

Oleg

[lbreddemann · Accepted solution]

See GROUPING_ID - SAP HANA SQL and System Views Reference - SAP Library

and GROUPING - SAP HANA SQL and System Views Reference - SAP Library

Indeed the output of GROUPING seems to be exactly inverted to what the documentation tells us.

In this example GROUPING returns  0 for columns used in the current grouping.

GROUPING_ID seems to be calculated correctly though:

first group :    ((COUNTRY, LANGU,  "SHORT TEXT"))

bit vector:         0     0        1        -> 001 -> 1

second group:  ((COUNTRY, LANGU , "SHORT TEXT"))

bit vector:         1      0       0        -> 100 -> 4

- Lars

[Former Member · Accepted solution]

Thanks Lars.

I just was wondering why I'm getting different results as showed in the documentation.

[alanmcshane · Accepted solution]

FYI I found a couple of bugs with GROUPING about a month ago and raised an internal bug.

The 2 issues are...

1. GROUPING( <column name> ) returns 1 when should be 0 and 0 when should be 1

2. GROUPING can return values > 1.

Good news - the issue has been fixed internally so is just waiting to be merged into internal releases/testing etc.

regards,

Alan

[lbreddemann · Accepted solution]

Great feedback on these issues Alan!

Thanks for sharing this.

I guess the GROUPING SETS related functions are heavily underused, so there might still be a few surprises in there...

- Lars