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

–***–


About this document …

Cross-tab dengan MySQL

This document was generated using the
LaTeX2HTML translator Version 2K.1beta (1.47)

Copyright © 1993, 1994, 1995, 1996,
Nikos Drakos,
Computer Based Learning Unit, University of Leeds.

Copyright © 1997, 1998, 1999,
Ross Moore,
Mathematics Department, Macquarie University, Sydney.

The command line arguments were:

latex2html -split 0 cross-tab.tex

The translation was initiated by on 2005-07-25


2005-07-25

14 Responses to Cross-tab dengan mysql

  1. antares hadinata says:

    terima kasih atas sharingnya,……

  2. sodik says:

    mohon ijin copy paste untuk sy sharing di blog juga…
    terimakasih…

  3. admin says:

    Silakan, semoga bermanfaat.

  4. mohon ijinnya tuk di sharing kembali di blog saya… n btw, nice sharing gan… kebetulan saya lagi ada kasus seperti ini… Thx alot…

  5. admin says:

    Silakan, semoga bermanfaat bagi banyak orang.

  6. Roy says:

    Pak gimana cara klo transpose di MYSQL????
    klo itu tadi crosstab, klo merubah baris menjadi kolom gimana pak???
    Mohon bantuannya, klo bisa contoh di php mysql skalian…
    makasih banyak…

  7. admin says:

    Untuk transpose, salah satu cara yang mudah adalah menampung hasil query ke dalam array 2 dimensi, kemudian mencetaknya ke dalam format yang diinginkan dengan menukar indeks kolom dan baris.

    Dalam contoh berikut digunakan variabel $array sebagai array 2 dimensi dan mencetaknya dalam format tabel html.
    Silakan disimak!

    < ?php
    mysql_connect('localhost','user','password');
    mysql_select_db('db');
    $sql='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;';
    $query=mysql_query($sql) or Die(mysql_error());
    $array=array();
    while($row=mysql_fetch_row($query)) {
    	$array[]=$row;
    }
    print_r($array);
    echo "";
    for($i=0;$i";
    	for($j=0;$j".$array[$j][$i];
    	}
    	echo "\n";
    }
    echo "";
    ?>
    
    
  8. djeri says:

    saya juga ijin copas buat belajar dan dishare kembali om 🙂

  9. chandra says:

    pak, bagaimana klo kasusnya seperti ini :

    A B C D E F G
    ————-
    1 9 1 1 2 8 4
    1 1 5 6 7 3 1
    1 1 1 1 1 2 2
    1 0 6 4 3 1 2

    nah kita ingin mengambil yang varian nilainya paling sedikit (record nomor 3)

  10. Thanks for finally talking about > Curah coret – Yohanes Suyanto

  11. Escoge otros arreglos que no sean florales: eventos en lima 2015 septiembre
    lugar de ocupar las mesas con flores naturales puedes abaratar costos incluyendo otros elementos ornamentales como lámparas, fanales,
    centros de mesas con candelas , frutas con flores individuales.

  12. Leave a Reply

    Your email address will not be published. Required fields are marked *