-1

I am trying to understand an SQL written by a colleague and I am struggling to understand how does the GROUPING() function work in this case. Why is the value of date_level_year_quarter_month equal to 3 and 7 in the last two rows? Thank you in advance!

SELECT
    t_year,
    t_quarter,
    t_month,
    GROUPING(t_year, t_quarter, t_month) date_level_year_quarter_month
FROM
    d_time
WHERE t_year IN (2016)
GROUP BY
ROLLUP(t_year, t_quarter, t_month)
ORDER BY 1, 2, 3;

+---------+------------+------------+-------------------------------+
| t_year  |  t_quarter |  t_month   | date_level_year_quarter_month |
+---------+------------+------------+-------------------------------+
| 2016    |      1     |      1     |               0               |
+---------+------------+------------+-------------------------------+
| 2016    |      1     |      2     |               0               |
+---------+------------+------------+-------------------------------+
| 2016    |      1     |      3     |               0               |
+---------+------------+------------+-------------------------------+
| 2016    |      1     |            |               1               |
+---------+------------+------------+-------------------------------+
| 2016    |    ...     |     ...    |              ...              |
+---------+------------+------------+-------------------------------+
| 2016    |            |            |               3               |
+---------+------------+------------+-------------------------------+
|         |            |            |               7               |
+---------+------------+------------+-------------------------------+

EDIT: It looks like for every dimension 'added', the value of GROUPING function is two times the value of 'previous grouping' + 1, however it is still unclear to me, how the function calculates this values in the first place.

SELECT 
      T_YEAR
    , T_QUARTAL
    , T_MONTH
    , T_WEEK
    , T_DATE
    , GROUPING (T_YEAR ) AS LEVEL_YEAR
    , GROUPING (T_YEAR, T_QUARTER) AS LEVEL_YEAR_QUARTAL
    , GROUPING (T_YEAR, T_QUARTER, T_MONTH) AS LEVEL_YEAR_QUARTAL_MONTH
    , GROUPING (T_YEAR, T_QUARTER, T_MONTH, T_WEEK) AS LEVEL_YEAR_QUARTAL_MONTH_WEEK
    , GROUPING (T_YEAR, T_QUARTER, T_MONTH, T_WEEK, T_DAY) AS LEVEL_YEAR_QUARTAL_MONTH_WEEK_DAY
FROM D_TIME
WHERE T_YEAR=2016 AND T_WEEK = 1
GROUP BY
ROLLUP (T_YEAR, T_QUARTER, T_MONTH, T_WEEK, T_DAY) 
ORDER BY 5,1,2,3,4;

Grouping output:

Janna Sherazi
  • 167
  • 1
  • 1
  • 15
  • Which [DBMS product](https://en.wikipedia.org/wiki/DBMS) are you using? "SQL" is just a query language, not the name of a specific database product. Please add a tag for the database product you are using `postgresql`, `oracle`, `sql-server`, `db2`, ... –  May 03 '18 at 08:48
  • Why don't you ask your colleague? – HoneyBadger May 03 '18 at 08:49
  • @HoneyBadger he wrote it in 2013 and is long gone from the company ;) – Janna Sherazi May 03 '18 at 08:51
  • The ROLLUP function is a normal group by, but it also add rows for the subtotals and total (you can see them where the columns quarter/month are null). Is that's what you asking ? – Thomas G May 03 '18 at 08:54
  • @ThomasG thanks for answer. What I am trying to find out is how in this case the GROUPING(t_year, t_quarter, t_month) sums up to 7. I know it should return 1 in superaggregate rows, but how does it end up being 7 in this case? – Janna Sherazi May 03 '18 at 08:59
  • it should be your "grand total". So the total for all months of all years – Thomas G May 03 '18 at 09:01
  • Interesting question. I thought `GROUPING()` only took one argument. – Gordon Linoff May 03 '18 at 11:18
  • provide your data – Kaval Patel May 03 '18 at 11:29
  • Hi @KavalPatel the d_time table is a dimension table including time data (such as year, quarter, month, week, day) on a daily level. – Janna Sherazi May 03 '18 at 11:58

1 Answers1

0

I've found the answer in Exasol documentation.

"In case of multiple arguments the result value is a number whose binary representation is similar to

GROUPING(arg1), GROUPING(arg2),..., GROUPING(argn). 

E.g. the following is true:

GROUPING(a,b,c) = 4xGROUPING(a) + 2xGROUPING(b) + 1xGROUPING(c)."
Janna Sherazi
  • 167
  • 1
  • 1
  • 15