GROUPING SETS Clause

Note : I’m using SQL Server 2008 for this tutorial.

I found interesting clause named by GROUPING SETS, it can be used to simplify following query :

[code language=”sql”]
SELECT nama, NULL,  SUM(amount)FROM
(
SELECT ‘arif’ AS nama, 1 AS transaksi, 1000 AS amount UNION ALL
SELECT ‘arif’ AS nama, 1 AS transaksi, 1000 AS amount UNION ALL
SELECT ‘arif’ AS nama, 1 AS transaksi, 900 AS amount UNION ALL
SELECT ‘arif’ AS nama, 1 AS transaksi, 800 AS amount UNION ALL
SELECT ‘nasution’ AS nama, 2 AS transaksi, 700 AS amount UNION ALL
SELECT ‘nasution’ AS nama, 2 AS transaksi, 600 AS amount
) c
GROUP BY nama

UNION ALL

SELECT NULL, transaksi,  SUM(amount)FROM
(
SELECT ‘arif’ AS nama, 1 AS transaksi, 1000 AS amount UNION ALL
SELECT ‘arif’ AS nama, 1 AS transaksi, 1000 AS amount UNION ALL
SELECT ‘arif’ AS nama, 1 AS transaksi, 900 AS amount UNION ALL
SELECT ‘arif’ AS nama, 1 AS transaksi, 800 AS amount UNION ALL
SELECT ‘nasution’ AS nama, 2 AS transaksi, 700 AS amount UNION ALL
SELECT ‘nasution’ AS nama, 2 AS transaksi, 600 AS amount
) c
GROUP BY transaksi
[/code]

With “GROUPING SETS”  statement, query above become like following query :

[code language=”sql”]
SELECT nama, transaksi,  SUM(amount)FROM
(
SELECT ‘arif’ AS nama, 1 AS transaksi, 1000 AS amount UNION ALL
SELECT ‘arif’ AS nama, 1 AS transaksi, 1000 AS amount UNION ALL
SELECT ‘arif’ AS nama, 1 AS transaksi, 900 AS amount UNION ALL
SELECT ‘arif’ AS nama, 1 AS transaksi, 800 AS amount UNION ALL
SELECT ‘nasution’ AS nama, 2 AS transaksi, 700 AS amount UNION ALL
SELECT ‘nasution’ AS nama, 2 AS transaksi, 600 AS amount
) c
GROUP BY GROUPING SETS (nama, transaksi)
[/code]

Two query above meant to create sum of amount by “nama” or “transaksi” into 1 result.

I hope this help.

Marifnst, 2013-08-28