Skip to Content
0
Former Member
Mar 04, 2015 at 01:27 PM

SAP HANA Usage of Grouping

241 Views

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