text: -- SQL/DB ALL IN ONE 01
  -- MAY 3, 2021
  
  -- #01. GROUP BY
  
  -- Syntax
  SELECT 
  	COLUMN_1
  	, AGGREGATIONAL_FUNC(COLUMN_2)
  FROM 
  	TABLE_NAME
  GROUP BY
  	COLUMN_1
  	;
  	
  
  -- Ex#01. Simple group by
  SELECT
  	CUSTOMER_ID
  FROM 
  	PAYMENT
  GROUP BY 
  	CUSTOMER_ID -- OMITTING SAME VALUES
  	;
  	
  -- Ex#02. SUM + SORTING wit GROUP BY
  SELECT
  	  CUSTOMER_ID
  	, SUM(AMOUNT) AS AMOUNT_SUM
  FROM
  	PAYMENT
  GROUP BY 
  	CUSTOMER_ID
  ORDER BY 
  	SUM(AMOUNT) desc
  	;
  
  -- Ex#03 COUNT with GROUP BY
  SELECT
  	STAFF_ID
  	, COUNT(PAYMENT_ID) AS COUNT
  FROM 
  	PAYMENT
  GROUP BY 
  	STAFF_ID
  	;
  
  -- #02. HAVING
  -- GROUP BY MUST BE WRTITTEN RIGHT AT THE BACK OF 
  -- FROM OR WHERE.
  -- Syntax
  SELECT 
  	COLUMN_1
  	, FUNCTION(COLUMN_2)
  FROM
  	TABLE_NAME
  GROUP BY
  	COLUMN_1 HAVING CONDITION;
  	
  -- Ex#01. GROUP BY + HAVING
  SELECT
  	  CUSTOMER_ID
  	, SUM(AMOUNT) AS AMOUNT_SUM
  FROM
  	PAYMENT
  GROUP BY 
  	CUSTOMER_ID HAVING SUM(AMOUNT) > 200
  	;
  	
  -- #03 GROUPING SET
  -- IT GIVES SAME RESULT WITH UNION ALL
  
  CREATE TABLE SALES
  (
  BRAND VARCHAR NOT NULL,
  SEGMENT VARCHAR NOT NULL,
  QUANTITY INT NOT NULL,
  PRIMARY KEY (BRAND, SEGMENT)
  );
  
  INSERT INTO SALES (BRAND, SEGMENT, QUANTITY)
  VALUES
  ('ABC', 'Premium', 100)
  , ('ABC', 'Basic', 200)
  , ('XYZ', 'Premium', 100)
  , ('XYZ', 'Basic', 300);
  
  -- Syntax
  SELECT 
  	  C1
  	, C2
  	, FUNCTION(C3)
  FROM
  	TABLE_NAME
  GROUP BY
  GROUPING SETS
  	(
  	(C1, C2)
  	, (C1)
  	, (C2)
  	, ()
  	)
  	;
  
  -- Ex#01 Simple grouping set
  
  SELECT
  	  BRAND
  	, SEGMENT
  	, SUM(QUANTITY) 
  FROM
  	SALES
  GROUP BY
  	GROUPING SETS
  		(
  	 		  (BRAND, SEGMENT)
  			, (BRAND)
  			, (SEGMENT)
  			, ()
  		)
  	;
  
  -- Ex#02 Application of Grouping function
  SELECT
  	  GROUPING(BRAND) GROUPING_BRAND
  	, GROUPING(SEGMENT) GROUPING_SEGEMENT
  	, BRAND
  	, SEGMENT
  	, SUM (QUANTITY)
  FROM
  	SALES
  GROUP BY
  	GROUPING SETS
  	(
  		  (BRAND, SEGMENT)
  		, (BRAND)
  		, (SEGMENT)
  		, ()
  	)
  ORDER BY BRAND, SEGMENT
  	;
  
  
  -- #04 ROLL UP
  -- USED IN DESIGNATED GROUPING COLUMN'S SUB TOTAL
  -- syntax
  
  SELECT
  	  C1 
  	, C2
  	, C3
  	, function(C4)
  FROM 
  	TABLE_NAME
  GROUP BY
  	ROLLUP (C1, C2, C3)
  	;
  
  SELECT
  	  C1 
  	, C2
  	, C3
  	, function(C4)
  FROM 
  	TABLE_NAME
  GROUP BY 
  	  C1
  	, ROLLUP (C2, C3)
  	;
  SELECT
        BRAND
  	, SEGMENT
  	, QUANTITY
  FROM
  	SALES
  	;
  
  
  -- Ex01. 
  SELECT
        BRAND
  	, SEGMENT
  	, SUM (QUANTITY)
  FROM
  	SALES
  GROUP BY
  	ROLLUP (BRAND, SEGMENT)
  ORDER BY
  	  BRAND
  	, SEGMENT
  	;
  
  -- Ex02. 
  SELECT
  	  SEGMENT
  	, BRAND
  	, SUM (QUANTITY)
  FROM
  	SALES
  GROUP BY 
  	  SEGMENT
  	, ROLLUP (BRAND)
  ORDER BY
  	  SEGMENT
  	, BRAND
  	;
  	
  -- #06. CUBE
  -- USED IN DESIGNATED GROUPING COLUMN'S 
  -- MULTI-DIMENSIONAL ARRAYS'S SUBTOTAL.
  
  -- Syntax
  SELECT
  	  C1
  	, C2
  	, C3
  	, func(C4)
  FROM 
  	TABLE_NAME
  GROUP BY
  	CUBE (C1, C2, C3) -- <<< Multi-dimensional arrays. 2^n units of array. 
  	;
  	
  -- Ex01. 
  SELECT
  	  BRAND
  	, SEGMENT
  	, SUM (QUANTITY)
  FROM 
  	SALES
  GROUP BY
  	CUBE (BRAND, SEGMENT)
  ORDER BY
  	  BRAND
  	, SEGMENT
  	;