Samarth Diamond - Learning MS SQL Group By Options like Grouping Sets, Rollup, Cube

SET NOCOUNT ON;

SET TRANSACTION ISOLATION LEVEL READ UnCommitted;

Select RoughNo,PurityCode ,round(sum(InWt),6) PolWt

from PAMS.dbo.vFinalResult Res

Where RoughNo in (40528,40529) and PurityCode in (1,2)

GROUP BY GROUPING SETS ( RoughNo,PurityCode );

/*

RoughNo PurityCode PolWt

"[NULL]" "1" "6.759998"

"[NULL]" "2" "0.498"

"40,528" "[NULL]" "3.476996"

"40,529" "[NULL]" "3.781002"

*/

SET NOCOUNT ON;

SET TRANSACTION ISOLATION LEVEL READ UnCommitted;

Select RoughNo,PurityCode ,round(sum(InWt),6) PolWt

from PAMS.dbo.vFinalResult Res

Where RoughNo in (40528,40529) and PurityCode in (1,2)

GROUP BY ROLLUP ( RoughNo,PurityCode );

/*

GROUP BY ROLLUP (col1, col2, col3, col4) creates groups for each combination of column expressions in the following lists.

col1, col2, col3, col4

col1, col2, col3, NULL

col1, col2, NULL, NULL

col1, NULL, NULL, NULL

NULL, NULL, NULL, NULL --This is the grand total

RoughNo PurityCode PolWt

"40,528" "1" "3.294998"

"40,528" "2" "0.181998"

"40,528" "[NULL]" "3.476996"

"40,529" "1" "3.465"

"40,529" "2" "0.316002"

"40,529" "[NULL]" "3.781002"

"[NULL]" "[NULL]" "7.257998"

*/

SET NOCOUNT ON;

SET TRANSACTION ISOLATION LEVEL READ UnCommitted;

SELECT COALESCE(CAST(RoughNo AS varchar), 'Grand Total') AS RoughNo,

COALESCE(CAST(PurityCode AS varchar), 'Total') AS PurityCode,

ROUND(SUM(InWt), 6) AS PolWt

FROM PAMS.dbo.vFinalResult Res

WHERE RoughNo IN (40528,40529) and PurityCode in (1,2)

GROUP BY ROLLUP(RoughNo, PurityCode);

/*

RoughNo PurityCode PolWt

"40528" "1" "3.294998"

"40528" "2" "0.181998"

"40528" "Total" "3.476996"

"40529" "1" "3.465"

"40529" "2" "0.316002"

"40529" "Total" "3.781002"

"Grand Total" "Total" "7.257998"

*/

SELECT COALESCE(CAST(RoughNo AS varchar), 'Grand Total') AS RoughNo,

COALESCE(CAST(PurityCode AS varchar), 'Total') AS PurityCode,

ROUND(SUM(InWt), 6) AS PolWt

FROM PAMS.dbo.vFinalResult Res

WHERE RoughNo IN (40528,40529) and PurityCode in (1,2)

GROUP BY CUBE(RoughNo, PurityCode);

/*

GROUP BY CUBE creates groups for all possible combinations of columns. For GROUP BY CUBE (a, b) the results has groups for unique values of (a, b), (NULL, b), (a, NULL), and (NULL, NULL).

RoughNo PurityCode PolWt

"40528" "1" "3.294998"

"40529" "1" "3.465"

"Grand Total" "1" "6.759998"

"40528" "2" "0.181998"

"40529" "2" "0.316002"

"Grand Total" "2" "0.498"

"Grand Total" "Total" "7.257998"

"40528" "Total" "3.476996"

"40529" "Total" "3.781002"

*/

https://learn.microsoft.com/en-us/sql/t-sql/queries/select-group-by-transact-sql?view=sql-server-ver16