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
Leave a Reply