Cross-tab dengan mysql

Cross-tab atau cross tabulation adalah bentuk laporan statistik yang merupakan de-normalisasi dari data dab ditampilkan dalam bentuk kelompok (group) dari sebuah field dan kolomnya terdiri atas nilai-nilai yang berbeda dari suatu field lain.

Misalnya ada tabel mhs dengan isi sebagai berikut:

nama	seks	fakultas
Joni	P	Biologi
Hardi	P	Biologi
Anita	W	Psikologi
Kadir	P	Ilmu Budaya
Budiman	P	Teknik
Husin	P	Psikologi
Linda	W	Biologi
Kartono	P	Teknik
Kartini	W	Psikologi
Suciwati	W	Teknik
Legiman	P	Biologi
Kuntoro	P	Teknik
Susan	W	Biologi
Aniati	W	Teknik

Hasil cross-tab untuk kolom fakultas dan seks adalah:

fakultas	P	W	total
Biologi	3	2	5
Ilmu Budaya	1	0	1
Psikologi	1	2	3
Teknik	3	2	5

Pembuatan cross-tab secara manual

Tabel cross-tab tersebut dapat diperoleh dengan memanfaatkan fasilitas
query yang ada pada MySQL. Namun sebelum melihat bentuk query-nya, kita lihat
dulu penyelesaian masalah tersebut secara manual.

Kita buat tabel dengan kolom fakultas, P, dan W. Setelah itu kita urut
satu persatu baris dalam tabel mhs. Setiap menemukan data fakultas yang belum
ada di tabel kita tulis nama fakultas tersebut pada kolom fakultas. Untuk setiap
baris kita tambahkan nilai 1 pada kolom P jika seks bernilai \’P\’ dan kita tambahkan
nilai 1 pada kolom W jika seks bernilai \’W\’. Hasil tabel sementara sebagai berikut:

fakultas	P	W
Biologi	1+1	0+0

Ternyata mudah. Prinsipnya adalah jumlahkan ke kolom P jika bernilai \’P\’ dan
jumlahkan ke kolom W jika bernilai \’W\’.

Query I

Jika kata yang ditulis tebal pada kalimat sebelum ini diganti dengan Bahasa Inggris hasilnya
adalah: sum ke kolom P if bernilai ‘P’ dan sum ke kolom W
jika bernilai ‘W’. Atau dalam SQL menjadi:

mysql> SELECT fakultas, SUM(IF(seks=\'P\',1,0)) AS P,
    -> SUM(IF(seks=\'W\',1,0)) AS W
    -> FROM mhs
    -> GROUP BY fakultas;

Hasilnya:

fakultas	P	W
Biologi	3	2
Ilmu Budaya	1	0
Psikologi	1	2
Teknik	3	2

Bagaimana dengan kolom jumlah (total)? Mudah juga ubah sedikit SQL tersebut menjadi:

mysql> SELECT fakultas, SUM(IF(seks=\'P\',1,0)) AS P,
    -> SUM(IF(seks=\'W\',1,0)) AS W,
    -> COUNT(*) as total
    -> FROM mhs
    -> GROUP BY fakultas;

Hasilnya:

fakultas	P	W	total
Biologi	3	2	5
Ilmu Budaya	1	0	1
Psikologi	1	2	3
Teknik	3	2	5

Mungkin timbul pertanyaan kenapa ingin mencacah baris/record menggunakan
sum bukan count? Ternyata count dan sum
cara kerjanya mirip dan bisa dikatakan sama untuk kasus tertentu. Coba SQL berikut:

mysql> SELECT COUNT(*) from mhs;

Hasilnya:

count(*)

24

Sekarang ganti \’count(*)\’ pada SQL tersebut dengan \’sum(1)\’:

mysql> SELECT SUM(1) from mhs;

Hasilnya:

sum(1)

24

Query II

Pada kasus tersebut nama kolom sudah diketahui sebelumnya yaitu \’P\’ dan \’W\’. Bagaimana jika
isi kolom tidak atau belum diketahui sebelumnya? Misalnya untuk kasus tersebut tetapi nama
kolom adalah nama fakultas sedang baris berisi seks.

Pertama kita ambil nama-nama fakultas dengan SQL:

mysql> SELECT distinct fakultas from mhs;

Hasilnya:

fakultas

Biologi

Psikologi

Ilmu Budaya

Teknik

Anda bisa menyusun SQL secara manual dengan memasukkan sum/if untuk setiap fakultas.
Namun demikian ada cara yang lebih baik. Ubah SQL tersebut menjadi:

mysql> SELECT distinct concat(\', sum(if(fakultas=\"\',fakultas,
    -> \'\",1,0)) as `\',fakultas,\'`\') from mhs;

Hasilnya:

concat(‘, sum(if(fakultas=\”\”‘,

, sum(if(fakultas=\”Biologi\”,1,0)) as `Biologi`

, sum(if(fakultas=\”Psikologi\”,1,0)) as `Psikologi`

, sum(if(fakultas=\”Ilmu Budaya\”,1,0)) as `Ilmu Budaya`

, sum(if(fakultas=\”Teknik\”,1,0)) as `Teknik`

Terlihat bahwa kita dapat menyusun kode SQL dengan SQL. Dengan cut and paste maka
dapat disusun SQL sesuai dengan keinginan yaitu:

mysql>select seks
    ->, sum(if(fakultas=\" Biologi\",1,0)) as  `Biologi`
    ->, sum(if(fakultas=\" Psikologi\",1,0)) as  `Psikologi`
    ->, sum(if(fakultas=\" Ilmu Budaya\",1,0)) as  `Ilmu Budaya`
    ->, sum(if(fakultas=\" Teknik\",1,0)) as  `Teknik`
    ->, count(*) as total
    ->from mhs
    ->group by seks

Hasilnya:

seks	Biologi	Psikologi	Ilmu Budaya	Teknik	total
P	3	1	1	3	8
W	2	2	0	2	6

Berikut ini adalah sebuah contoh membuat Cross Tabulation pada MySQL dengan sebuah kasus tentang statistik jenis kelamin siswa di beberapa kelas.

1. Buatlah sebuah table siswa dengan struktur seperti dibawah ini.

CREATE TABLE `siswa` (
  `id` char(4) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `nama` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `kelamin` char(1) COLLATE utf8_unicode_ci DEFAULT NULL,
  `kelas` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

2. Masukan data sample siswa seperti yang ditunjukan dibawah ini.

+------+-------------------+---------+---------+
| id   | nama              | kelamin | kelas   |
+------+-------------------+---------+---------+
| 0001 | DENDIE SANJAYA    | L       | KELAS 1 |
| 0002 | YULIANTI          | W       | KELAS 1 |
| 0003 | PITRASCHA ADITYIA | L       | KELAS 1 |
| 0004 | SRI HARTATI       | w       | KELAS 2 |
| 0005 | RIZKI PRIHANTO    | L       | KELAS 2 |
| 0006 | RIANO OSKAR       | L       | KELAS 2 |
| 0007 | HERU SASONGKO     | L       | KELAS 3 |
| 0008 | KHEDA GINANJAR    | L       | KELAS 3 |
| 0009 | MIFTAH KAMALUDIN  | L       | KELAS 3 |
+------+-------------------+---------+---------+

3. Membuat query untuk menghasilkan Cross Tabulations dengan variable/ faktor jenis kelamin dan kelas

select kelas, sum(if(kelamin='L',1,0)) as 'PRIA', sum(if(kelamin='W',1,0)) as 'WANITA', count(*) as TOTAL
from siswa
group by kelas

Berikut ini penjelasan dari bagian penting query diatas

• sum(if(kelamin='L',1,0)) as 'PRIA' -> Mengartikan apabila menemukan data hurup L pada field KELAMIN maka lakukan penambahan sebanyak satu angka. kemudian simpan hasil perhitungannya di kolom bernama PRIA.
• sum(if(kelamin='W',1,0)) as 'WANITA' -> Mengartikan apabila menemukan data hurup W pada field KELAMIN maka lakukan penambahan sebanyak satu angka. kemudian simpan hasil perhitungannya di kolom bernama WANITA.
• count(*) as TOTAL -> Mengartikan melakukan perhitungan seluruh siswa baik yang berkelamin pria maupun wanita.
• group by kelas -> mengartikan data akan di tampilkan secara berkelompok berdasarkan kelas.

4. Berikut ini adalah hasil dari query Cross Tabulations diatas.

+---------+------+--------+-------+
| kelas   | PRIA | WANITA | TOTAL |
+---------+------+--------+-------+
| KELAS 1 |    2 |      1 |     3 |
| KELAS 2 |    2 |      1 |     3 |
| KELAS 3 |    3 |      0 |     3 |
+---------+------+--------+-------+

sumber:http://yanto.staff.ugm.ac.id/?page_id=55

http://dendieisme.blogspot.co.id/2011/07/mysql-cross-tabulations.html