/************************************/
/* PODCAST 09 - THE GROUP BY CLAUSE */
/************************************/

/**********************************/
/* TOPIC 01 - THE GROUP BY CLAUSE */
/**********************************/

USE [HOBBY_SHOP]
GO

SELECT --DISTINCT 
    [CUST_ID],
	COUNT(*)			AS [NO TRANSACTIONS], 
	SUM([TOTAL_PRICE])	AS [TOTAL SALES]
FROM [SALES].[INVOICE]
GROUP BY [CUST_ID]
ORDER BY CUST_ID DESC
GO

SELECT DISTINCT [CUST_ID] AS [CUSTOMER ID],
	COUNT(*)			AS [NO TRANSACTIONS], 
	SUM([TOTAL_PRICE])	AS [TOTAL SALES]
FROM [SALES].[INVOICE]
GROUP BY  [CUST_ID]
ORDER BY CUST_ID ASC
GO

SELECT DISTINCT [CUST_ID],
	COUNT(*)			AS [NO TRANSACTIONS], 
	CONVERT(DECIMAL(10,2),AVG([TOTAL_PRICE]))	AS [AVG_TOTAL SALES]
FROM [SALES].[INVOICE]
GROUP BY  [CUST_ID]
ORDER BY CUST_ID DESC
GO

SELECT DISTINCT [CUST_ID],
	COUNT(*)			AS [NO TRANSACTIONS], 
	CONVERT(DECIMAL(10,2),AVG([TOTAL_PRICE]))	AS [AVG_TOTAL SALES]
FROM [SALES].[INVOICE]
GROUP BY  [CUST_ID]
HAVING AVG([TOTAL_PRICE]) > 1000
ORDER BY CUST_ID DESC
GO

/********************************/
/* TOPIC 02 - GROUP BY W/ROLLUP */
/********************************/

USE [HOBBY_SHOP]
GO

SELECT 
	CUS.CUST_NAME,
	PRD.PROD_NAME,
	SUM(INV.TOTAL_PRICE) AS TOTAL_SALES
FROM [SALES].[INVOICE] INV
	JOIN [INVENTORY].[PRODUCT] PRD
		ON INV.PROD_ID = PRD.PROD_ID
	JOIN [SALES].[CUSTOMER] CUS
		ON INV.CUST_ID = CUS.CUST_ID
GROUP BY ROLLUP (
	CUS.CUST_NAME,
	PRD.PROD_NAME
	)
ORDER BY CUS.CUST_NAME,PRD.PROD_NAME
GO

SELECT 
	CASE
		WHEN CUS.CUST_NAME IS NULL THEN 'ROLLUP BY CUSTOMERS'
		ELSE  CUS.CUST_NAME
	END AS CUST_NAME,

	CASE
		WHEN PRD.PROD_TYPE IS NULL 
			AND CUS.CUST_NAME IS NOT NULL THEN 'ROLLUP BY PRODUCT TYPE'
		ELSE  PRD.PROD_TYPE
	END AS PROD_TYPE,

	CASE
		WHEN PRD.PROD_TYPE IS NOT NULL 
			AND CUS.CUST_NAME IS NOT NULL 
				AND PRD.PROD_NAME IS NULL THEN 'ROLLUP BY PRODUCT'
		ELSE  PRD.PROD_NAME
	END AS PROD_NAME,

	--PRD.PROD_NAME,
	SUM(INV.TOTAL_PRICE) TOTAL_SALES
FROM [SALES].[INVOICE] INV
JOIN [INVENTORY].[PRODUCT] PRD
ON INV.PROD_ID = PRD.PROD_ID
JOIN [SALES].[CUSTOMER] CUS
ON INV.CUST_ID = CUS.CUST_ID
GROUP BY ROLLUP (
	CUS.[CUST_NAME],
	PRD.[PROD_TYPE],
	PRD.[PROD_NAME]
	)
ORDER BY CUS.CUST_NAME,PRD.PROD_TYPE,PRD.PROD_NAME
GO

/********************************/
/* TOPIC 03 - GROUP BY AND CUBE */
/********************************/

USE [HOBBY_SHOP]
GO

SELECT 
	CUS.CUST_NAME			AS [Customer Name],
	PRD.PROD_TYPE			AS [Product Type],
	PRD.PROD_NAME		    AS [Product Name],
	SUM(INV.TOTAL_PRICE) [Total Sales]
FROM [SALES].[INVOICE] INV
JOIN [INVENTORY].[PRODUCT] PRD
	ON INV.PROD_ID = PRD.PROD_ID
JOIN [SALES].[CUSTOMER] CUS
	ON INV.CUST_ID = CUS.CUST_ID
GROUP BY CUBE (
	CUS.CUST_NAME,
	PRD.PROD_TYPE,
	PRD.PROD_NAME
	)
ORDER BY CUS.CUST_NAME,PRD.PROD_NAME
GO

/****************************/
/* TOPIC 04 - GROUPING SETS */
/****************************/

USE [HOBBY_SHOP]
GO

SELECT 
	CUS.CUST_NAME,
	PRD.PROD_NAME,
	SUM(INV.TOTAL_PRICE) TOTAL_SALES

FROM [SALES].[INVOICE] INV
	JOIN [INVENTORY].[PRODUCT] PRD
		ON INV.PROD_ID = PRD.PROD_ID
	JOIN [SALES].[CUSTOMER] CUS
		ON INV.CUST_ID = CUS.CUST_ID

GROUP BY GROUPING SETS (
ROLLUP ( -- set 1
	CUS.CUST_NAME,
	PRD.PROD_NAME
	),
CUBE ( -- set 2
	CUS.CUST_NAME,
	PRD.PROD_NAME
	)
)
ORDER BY CUS.CUST_NAME,PRD.PROD_NAME
GO

/**********************************/
/* Run below in a seperate window */
/**********************************/

SELECT 
	CUS.CUST_NAME,
	PRD.PROD_NAME,
	SUM(INV.TOTAL_PRICE) TOTAL_SALES

FROM [SALES].[INVOICE] INV
	JOIN [INVENTORY].[PRODUCT] PRD
		ON INV.PROD_ID = PRD.PROD_ID
	JOIN [SALES].[CUSTOMER] CUS
		ON INV.CUST_ID = CUS.CUST_ID

GROUP BY GROUPING SETS (

/* ROLLUP ( -- set 1
	CUS.CUST_NAME,
	PRD.PROD_NAME
	)
	,
*/

CUBE ( -- set 2
	CUS.CUST_NAME,
	PRD.PROD_NAME
	)

)
ORDER BY CUS.CUST_NAME,PRD.PROD_NAME
GO

/**********************************/
/* Run below in a seperate window */
/**********************************/

SELECT 
	CUS.CUST_NAME,
	PRD.PROD_NAME,
	SUM(INV.TOTAL_PRICE) TOTAL_SALES

FROM [SALES].[INVOICE] INV
	JOIN [INVENTORY].[PRODUCT] PRD
		ON INV.PROD_ID = PRD.PROD_ID
	JOIN [SALES].[CUSTOMER] CUS
		ON INV.CUST_ID = CUS.CUST_ID

GROUP BY GROUPING SETS (

ROLLUP ( -- set 1
	CUS.CUST_NAME,
	PRD.PROD_NAME
	)

/*,

CUBE ( -- set 2
	CUS.CUST_NAME,
	PRD.PROD_NAME
	)
*/
)
ORDER BY CUS.CUST_NAME,PRD.PROD_NAME
GO

/*********************************************/
/* TOPIC 05 - GROUPING SETS WITH EMPTY GROUP */
/*********************************************/

USE [HOBBY_SHOP]
GO

SELECT 
	CUS.[CUST_NAME],
	PRDT.[PROD_TYPE],
	PRD.[PROD_NAME],
	SUM(INV.TOTAL_PRICE) TOTAL_SALES
FROM [SALES].[INVOICE] INV
	JOIN [INVENTORY].[PRODUCT] PRD
		ON INV.PROD_ID = PRD.PROD_ID
	JOIN [INVENTORY].[PRODUCT_TYPE] PRDT
		ON PRD.PROD_TYPE = PRDT.PROD_TYPE
	JOIN [SALES].[CUSTOMER] CUS
		ON INV.CUST_ID = CUS.CUST_ID
GROUP BY GROUPING SETS (
	CUS.[CUST_NAME],
	PRDT.[PROD_TYPE],
	PRD.[PROD_NAME],()
)
ORDER BY CUS.CUST_NAME,	PRDT.[PROD_TYPE],PRD.PROD_NAME
GO

/************************/
/* VERSUS NO EMPTY SETS */
/************************/

-- NO GRAND TOTAL

SELECT 
	CUS.[CUST_NAME],
	PRD.[PROD_TYPE],
	PRD.[PROD_NAME],
	SUM(INV.TOTAL_PRICE) TOTAL_SALES
FROM [SALES].[INVOICE] INV
JOIN [INVENTORY].[PRODUCT] PRD
ON INV.PROD_ID = PRD.PROD_ID
JOIN [SALES].[CUSTOMER] CUS
ON INV.CUST_ID = CUS.CUST_ID
GROUP BY GROUPING SETS (
	CUS.[CUST_NAME],
	PRD.[PROD_TYPE],
	PRD.[PROD_NAME]
)
ORDER BY CUS.[CUST_NAME],PRD.[PROD_TYPE],PRD.[PROD_NAME]
GO


/*************/
/* REVISED   */
/* 6/22/2020 */
/*************/

USE [HOBBY_SHOP]
GO

/************/
/* TOPIC 01 */
/************/

SELECT 
    INV.CUST_ID,
    CUS.CUST_NAME,
    INV.PROD_ID,
    PRD.PROD_NAME,
    SUM(INV.TOTAL_PRICE) TOTAL_SALES
FROM [SALES].[INVOICE] INV
    JOIN [INVENTORY].[PRODUCT] PRD
        ON INV.PROD_ID = PRD.PROD_ID
    JOIN [SALES].[CUSTOMER] CUS
        ON INV.CUST_ID = CUS.CUST_ID
GROUP BY 
    INV.CUST_ID,
    CUS.CUST_NAME,
    INV.PROD_ID,
    PRD.PROD_NAME
ORDER BY 
    CUS.CUST_NAME,PRD.PROD_NAME
GO

/*********************************/
/* TOPIC 02 GROUP BY WITH ROLLUP */
/*********************************/

SELECT 
    CUS.CUST_NAME,
    PRD.PROD_NAME,
    SUM(INV.TOTAL_PRICE) AS TOTAL_SALES
FROM [SALES].[INVOICE] INV
JOIN [INVENTORY].[PRODUCT] PRD
    ON INV.PROD_ID = PRD.PROD_ID
JOIN [SALES].[CUSTOMER] CUS
    ON INV.CUST_ID = CUS.CUST_ID
GROUP BY ROLLUP (
    CUS.CUST_NAME,
    PRD.PROD_NAME
)
ORDER BY CUS.CUST_NAME,PRD.PROD_NAME
GO

/**********************/
/* GROUP BY WITH CUBE */
/**********************/

SELECT 
     CUS.CUST_NAME,
     PRD.PROD_NAME,
     SUM(INV.TOTAL_PRICE) TOTAL_SALES
FROM [SALES].[INVOICE] INV
JOIN [INVENTORY].[PRODUCT] PRD
     ON INV.PROD_ID = PRD.PROD_ID
JOIN [SALES].[CUSTOMER] CUS
     ON INV.CUST_ID = CUS.CUST_ID
GROUP BY CUBE (
     CUS.CUST_NAME,
     PRD.PROD_NAME
)
ORDER BY CUS.CUST_NAME,PRD.PROD_NAME
GO


/****************************/
/* TOPIC 04 - GROUPING SETS */
/****************************/

SELECT 
     CUS.CUST_NAME,
     PRD.PROD_NAME,
     SUM(INV.TOTAL_PRICE) TOTAL_SALES
FROM [SALES].[INVOICE] INV
JOIN [INVENTORY].[PRODUCT] PRD
     ON INV.PROD_ID = PRD.PROD_ID
JOIN [SALES].[CUSTOMER] CUS
     ON INV.CUST_ID = CUS.CUST_ID
GROUP BY GROUPING SETS (
     ROLLUP ( -- set 1
          CUS.CUST_NAME,
          PRD.PROD_NAME
          ),
      CUBE ( -- set 2
          CUS.CUST_NAME,
          PRD.PROD_NAME
        )
     )
ORDER BY CUS.CUST_NAME,PRD.PROD_NAME
GO

/*****************************************/
/* TOPIC 05 - GROUPING SETS/EMPTY  GROUP */
/*****************************************/

SELECT 
	CUS.[CUST_NAME],
	PRD.[PROD_TYPE],
	PRD.[PROD_NAME],
	SUM(INV.TOTAL_PRICE) TOTAL_SALES
FROM [SALES].[INVOICE] INV
JOIN [INVENTORY].[PRODUCT] PRD
ON INV.PROD_ID = PRD.PROD_ID
JOIN [SALES].[CUSTOMER] CUS
ON INV.CUST_ID = CUS.CUST_ID
GROUP BY GROUPING SETS (
	CUS.[CUST_NAME],
	PRD.[PROD_TYPE],
	PRD.[PROD_NAME],()
)
ORDER BY CUS.CUST_NAME,	PRD.[PROD_TYPE],PRD.PROD_NAME
GO