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