------------------------------------------------------------
-- ROLLUP example
------------------------------------------------------------
SET linesize 150;
COLUMN m_desc format a15;
COLUMN t_cal_month_desc format a15;

SELECT   m_desc, t_cal_month_desc, n_iso_code, 
	 SUM(s_amnt_sold)
FROM     bi.tpch
WHERE    m_desc IN ('Direct Sales', 'Internet')
AND      t_cal_month_desc IN ('2000-09', '2000-10')
AND      n_iso_code IN ('GB', 'US')
GROUP BY ROLLUP(m_desc, t_cal_month_desc, n_iso_code);

quit;
------------------------------------------------------------
-- Partial ROLLUP example
------------------------------------------------------------
SELECT   m_desc, t_cal_month_desc, n_iso_code, 
	 SUM(s_amnt_sold)
FROM     bi.spctmn
WHERE    m_desc IN ('Direct Sales', 'Internet')
AND      t_cal_month_desc IN ('2000-09', '2000-10')
AND      n_iso_code IN ('GB', 'US')
GROUP BY m_desc, ROLLUP(t_cal_month_desc, n_iso_code);

quit;
------------------------------------------------------------
-- CUBE example
------------------------------------------------------------
SET linesize 150;
COLUMN m_desc format a15;
COLUMN t_cal_month_desc format a15;

SELECT   m_desc, t_cal_month_desc, n_iso_code, 
	 SUM(s_amnt_sold)
FROM     bi.spctmn
WHERE    m_desc IN ('Direct Sales', 'Internet')
AND      t_cal_month_desc IN ('2000-09', '2000-10')
AND      n_iso_code IN ('GB', 'US')
GROUP BY CUBE(m_desc, t_cal_month_desc, n_iso_code);

quit;
------------------------------------------------------------
-- GROUPING SETS example
------------------------------------------------------------
SET linesize 150;
COLUMN m_desc format a15;
COLUMN t_cal_month_desc format a15;

SELECT   m_desc, t_cal_month_desc, n_iso_code, 
	 SUM(s_amnt_sold)
FROM     bi.spctmn
WHERE    m_desc IN ('Direct Sales', 'Internet')
AND      t_cal_month_desc IN ('2000-09', '2000-10')
AND      n_iso_code IN ('GB', 'US')
GROUP BY GROUPING SETS((m_desc, t_cal_month_desc, n_iso_code),
                       (m_desc, n_iso_code),
		       (t_cal_month_desc, n_iso_code));

quit;

------------------------------------------------------------
-- GROUPING example
------------------------------------------------------------
SET linesize 150;
COLUMN m_desc format a15;
COLUMN t_cal_month_desc format a15;

SELECT   m_desc, t_cal_month_desc, n_iso_code,
	 SUM(s_amnt_sold),
	 GROUPING(m_desc) AS M,
	 GROUPING(t_cal_month_desc) AS T,
	 GROUPING(n_iso_code) AS N
FROM     bi.spctmn
WHERE    m_desc IN ('Direct Sales', 'Internet')
AND      t_cal_month_desc IN ('2000-09', '2000-10' )
AND      n_iso_code IN ('GB', 'US')
GROUP BY ROLLUP(m_desc, t_cal_month_desc, n_iso_code);

quit;


------------------------------------------------------------
-- GROUPING ID example
------------------------------------------------------------
SET linesize 150;
COLUMN m_desc format a15;
COLUMN t_cal_month_desc format a15;

SELECT   
	CASE WHEN grouping_id(m_desc)=1 THEN '*' ELSE m_desc END,
	CASE WHEN grouping_id(n_iso_code)=1 THEN '*' ELSE n_iso_code END,
	SUM(s_amnt_sold)
FROM     bi.spctmn
WHERE    m_desc IN ('Direct Sales', 'Internet')
AND      t_cal_month_desc = '2000-09'
AND      n_iso_code IN ('GB', 'US')
GROUP BY CUBE(m_desc, n_iso_code);

quit;


