Berikut adalah cara penggunaan Pivot Pada Mysql
1. Buat Table, Isi data , Query data
1.1. Buat Table : Sales
Field :
id_trx,
nama,
tgl_trx,
tim,
nilai_trx
1.2. Isi data :
id_trx nama tgl_trx tim nilai_trx
1 Alfa 2016-01-10 1 250000
2 Charlie 2016-01-02 2 175000
3 Bravo 2016-01-01 1 310000
4 Bravo 2016-02-04 1 250000
5 Alfa 2016-01-15 1 300000
6 Charlie 2016-01-13 2 325000
7 Bravo 2016-02-07 1 275000
8 Bravo 2016-03-06 1 150000
9 Alfa 2016-02-05 1 215000
10 Alfa 2016-02-22 1 350000
11 Alfa 2016-02-02 1 450000
12 Alfa 2016-03-12 1 150000
13 Alfa 2016-03-17 1 225000
14 Bravo 2016-03-11 1 150000
15 Bravo 2016-03-18 1 150000
16 Charlie 2016-01-23 2 350000
17 Charlie 2016-02-22 2 300000
18 Charlie 2016-03-21 2 275000
19 Charlie 2016-03-12 2 450000
20 Delta 2016-02-11 2 450000
21 Delta 2016-02-17 2 550000
22 Delta 2016-03-11 2 370000
1.3. Query data :
SELECT nama,
SUM( IF( MONTH(tgl_trx) = 1, nilai_trx, 0) ) AS januari,
SUM( IF( MONTH(tgl_trx) = 2, nilai_trx, 0) ) AS februari,
SUM( IF( MONTH(tgl_trx) = 3, nilai_trx, 0) ) AS maret,
SUM( nilai_trx ) AS total_trx
FROM tabel_sales
GROUP BY nama
Output :
1.4. Menampilkan data lengkap
SELECT
nama,
SUM( IF( MONTH(tgl_trx) = 1, nilai_trx, 0) ) AS januari,
COUNT( IF( MONTH(tgl_trx) = 1, id_trx, NULL) ) AS trx_1,
SUM( IF( MONTH(tgl_trx) = 2, nilai_trx, 0) ) AS februari,
COUNT( IF( MONTH(tgl_trx) = 2, nilai_trx, NULL) ) AS trx_2,
SUM( IF( MONTH(tgl_trx) = 3, nilai_trx, 0) ) AS maret,
COUNT( IF( MONTH(tgl_trx) = 3, nilai_trx, NULL) ) AS trx_3,
COUNT(id_trx) AS jml_trx,
SUM( nilai_trx ) AS total_trx
FROM tabel_sales
GROUP BY nama
Output :
2. Pivot : Pivot Table Dengan MySQL Dengan WITH ROLLUP
Contoh 1 :
SELECT IFNULL(nama, 'TOTAL') AS nama_sales,
SUM( IF( MONTH(tgl_trx) = 1, nilai_trx, 0) ) AS januari,
SUM( IF( MONTH(tgl_trx) = 2, nilai_trx, 0) ) AS februari,
SUM( IF( MONTH(tgl_trx) = 3, nilai_trx, 0) ) AS maret,
COUNT(id_trx) AS jml_trx,
SUM( nilai_trx ) AS total_trx
FROM tabel_sales
GROUP BY nama
WITh ROLLUP
Output :
Contoh 2 :
SELECT
IFNULL( nama, 'SUB TOTAL' ) AS nama_sales,
IFNULL( tim, 'TOTAL' ) AS sales_tim,
SUM( IF( MONTH(tgl_trx) = 1, nilai_trx, 0) ) AS januari,
COUNT( IF( MONTH(tgl_trx) = 1, id_trx, NULL) ) AS trx_1,
SUM( IF( MONTH(tgl_trx) = 2, nilai_trx, 0) ) AS februari,
COUNT( IF( MONTH(tgl_trx) = 2, nilai_trx, NULL) ) AS trx_2,
SUM( IF( MONTH(tgl_trx) = 3, nilai_trx, 0) ) AS maret,
COUNT( IF( MONTH(tgl_trx) = 3, nilai_trx, NULL) ) AS trx_3,
COUNT(id_trx) AS jml_trx,
SUM( nilai_trx ) AS total_trx
FROM tabel_sales
GROUP BY tim, nama
WITH ROLLUP
Output :
Contoh 3 :
SELECT
IFNULL( nama, 'SUB TOTAL' ) AS nama_sales,
IFNULL( tim, 'TOTAL' ) AS sales_tim,
SUM( IF( MONTH(tgl_trx) = 1, nilai_trx, 0) ) AS januari,
COUNT( IF( MONTH(tgl_trx) = 1, id_trx, NULL) ) AS trx_1,
SUM( IF( MONTH(tgl_trx) = 2, nilai_trx, 0) ) AS februari,
COUNT( IF( MONTH(tgl_trx) = 2, nilai_trx, NULL) ) AS trx_2,
SUM( IF( MONTH(tgl_trx) = 3, nilai_trx, 0) ) AS maret,
COUNT( IF( MONTH(tgl_trx) = 3, nilai_trx, NULL) ) AS trx_3,
COUNT(id_trx) AS jml_trx,
SUM( nilai_trx ) AS total_trx
FROM tabel_sales
GROUP BY tim DESC, nama DESC
WITH ROLLUP
Output :
Contoh 4 :
SELECT * FROM
(
SELECT
IFNULL( nama, '1 - SUB TOTAL' ) AS nama_sales,
IFNULL( tim, '0 - TOTAL' ) AS sales_tim,
SUM( IF( MONTH(tgl_trx) = 1, nilai_trx, 0) ) AS januari,
COUNT( IF( MONTH(tgl_trx) = 1, id_trx, NULL) ) AS trx_1,
SUM( IF( MONTH(tgl_trx) = 2, nilai_trx, 0) ) AS februari,
COUNT( IF( MONTH(tgl_trx) = 2, nilai_trx, NULL) ) AS trx_2,
SUM( IF( MONTH(tgl_trx) = 3, nilai_trx, 0) ) AS maret,
COUNT( IF( MONTH(tgl_trx) = 3, nilai_trx, NULL) ) AS trx_3,
COUNT(id_trx) AS jml_trx,
SUM( nilai_trx ) AS total_trx
FROM tabel_sales
GROUP BY tim, nama
WITH ROLLUP
) AS tabel_sales
ORDER BY sales_tim DESC, nama_sales DESC
Output :
Contoh 5 :
SELECT * FROM (
SELECT
nama,
tim,
SUM( IF( MONTH(tgl_trx) = 1, nilai_trx, 0) ) AS januari,
COUNT( IF( MONTH(tgl_trx) = 1, id_trx, NULL) ) AS trx_1,
SUM( IF( MONTH(tgl_trx) = 2, nilai_trx, 0) ) AS februari,
COUNT( IF( MONTH(tgl_trx) = 2, nilai_trx, NULL) ) AS trx_2,
SUM( IF( MONTH(tgl_trx) = 3, nilai_trx, 0) ) AS maret,
COUNT( IF( MONTH(tgl_trx) = 3, nilai_trx, NULL) ) AS trx_3,
COUNT(id_trx) AS jml_trx,
SUM( nilai_trx ) AS total_trx
FROM tabel_sales
GROUP BY tim, nama
ORDER BY total_trx DESC
) AS tabel_sales
UNION ALL
SELECT 'TOTAL' AS nama, '' AS tim,
SUM( IF( MONTH(tgl_trx) = 1, nilai_trx, 0) ) AS januari,
COUNT( IF( MONTH(tgl_trx) = 1, id_trx, NULL) ) AS trx_1,
SUM( IF( MONTH(tgl_trx) = 2, nilai_trx, 0) ) AS februari,
COUNT( IF( MONTH(tgl_trx) = 2, nilai_trx, NULL) ) AS trx_2,
SUM( IF( MONTH(tgl_trx) = 3, nilai_trx, 0) ) AS maret,
COUNT( IF( MONTH(tgl_trx) = 3, nilai_trx, NULL) ) AS trx_3,
COUNT(id_trx) AS jml_trx,
SUM( nilai_trx ) AS total_trx
FROM tabel_sales
Output :
