Rabu, 31 Juli 2019

Cara Mengurutkan Data (Sorting) dan grup di MySQL

Secara umum, sintak dasar untuk mengurutkan data di MySQL adalah seperti dibawah ini:

1SELECT kolom_1, kolom_2, ..., kolom_n
2ORDER BY kolom_1, [kolom_2] [ASC | DESC];



Penjelasan Sintak:
  • Keyword ASC digunakan untuk mengurutkan data secara Ascending.
  • Keyword DESC digunakan untuk mengurutkan data secara Descending.
  • Secara default, ketika ORDER BY digunakan tanpa opsi ASC atau DESC, maka data yang diurutkan adalah Ascending.
  • Anda bisa menggunakan opsi ASC dan DESC secara bersamaan sesuai dengan kebutuhan.
Anda juga bisa mengurutkan data berdasarkan posisi kolom suatu table, yaitu seperti dibawah ini:

1SELECT * FROM nama_table
2ORDER BY posisi_kolom [ASC | DESC];

Penjelasan Sintak:
  • posisi_kolom adalah posisi dari suatu kolom di table, seperti 1, 2, 3, dan seterusnya berdasarkan urutan kolom ketika tabel dibuat.

Contoh Query

Dibawah ini adalah contoh penggunaan query ORDER BY Clause,

1SELECT * FROM mahasiswa
2ORDER BY nama ASC;

Anda juga bisa menggunakan perintah ORDER BY Clause dengan posisi kolom seperti di bawah ini:

1SELECT * FROM mahasiswa
2ORDER BY 1 ASC;

Latihan


Sebagai latihan Bagaimana Cara Mengurutkan Data di MySQL, coba Anda ikuti step-step di bawah ini
  • Login ke database MySQL

    01# mysql -u root
    02Welcome to the MySQL monitor.  Commands end with ; or \g.
    03Your MySQL connection id is 1
    04Server version: 5.5.36 MySQL Community Server (GPL)
    05
    06Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
    07
    08Oracle is a registered trademark of Oracle Corporation and/or its
    09affiliates. Other names may be trademarks of their respective
    10owners.
    11
    12Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  • Pilih database yang akan digunakan

    1mysql> use akademik;
    2Database changed

  • Buatlah tabel MAHASISWA dengan menggunakan perintah sebagai berikut

    01mysql> CREATE TABLE mahasiswa(
    02    -> npm VARCHAR(10),
    03    -> nama VARCHAR(30),
    04    -> gender VARCHAR(1),
    05    -> asal VARCHAR(30),
    06    -> jurusan VARCHAR(30),
    07    -> ipk DOUBLE(3,2),
    08    -> PRIMARY KEY(npm)
    09    -> );
    10Query OK, 0 rows affected (0.15 sec)

  • Insert data ke tabel MAHASISWA dengan data sebagai berikut:

    01mysql> INSERT INTO mahasiswa
    02    -> VALUES('20151001','Nurul','P','Jakarta','Akuntansi',3.1);
    03Query OK, 1 row affected (0.07 sec)
    04
    05mysql> INSERT INTO mahasiswa
    06    -> VALUES('20152001','Budi','L','Bandung','Teknik Mesin',2.9);
    07Query OK, 1 row affected (0.06 sec)
    08
    09mysql> INSERT INTO mahasiswa
    10    -> VALUES('20153001','Ida','P','Semarang','Sastra Inggris',3.5);
    11Query OK, 1 row affected (0.11 sec)
    12
    13mysql> INSERT INTO mahasiswa
    14    -> VALUES('20154001','Eko','L','Jakarta','Teknik Informatika',3.25);
    15Query OK, 1 row affected (0.12 sec)
    16
    17mysql> INSERT INTO mahasiswa
    18    -> VALUES('20151002','Dina','P','Surabaya','Akuntansi',3.3);
    19Query OK, 1 row affected (0.06 sec)
    20
    21mysql> INSERT INTO mahasiswa
    22    -> VALUES('20154002','Doni','L','Makassar','Teknik Informatika',2.75);
    23Query OK, 1 row affected (0.07 sec)
    24
    25mysql> INSERT INTO mahasiswa
    26    -> VALUES('20154003','Dinda','P','Jakarta','Teknik Informatika',3.65);
    27Query OK, 1 row affected (0.04 sec)
    28
    29mysql> INSERT INTO mahasiswa
    30    -> VALUES('20151003','Naura','P','Bandung','Akuntansi',2.85);
    31Query OK, 1 row affected (0.09 sec)
    32
    33mysql> INSERT INTO mahasiswa
    34    -> VALUES('20153002','Soni','L','Surabaya','Sastra Inggris',3.3);
    35Query OK, 1 row affected (0.07 sec)
    36
    37mysql> INSERT INTO mahasiswa
    38    -> VALUES('20154004','Iski','P','Semarang','Teknik Informatika',3.8);
    39Query OK, 1 row affected (0.09 sec)
  • Tampilkan semua data Mahasiswa dan urutkan berdasarkan nama (ascending)

    01mysql> SELECT * FROM mahasiswa
    02    -> ORDER BY nama ASC;
    03+----------+-------+--------+----------+--------------------+------+
    04| npm      | nama  | gender | asal     | jurusan            | ipk  |
    05+----------+-------+--------+----------+--------------------+------+
    06| 20152001 | Budi  | L      | Bandung  | Teknik Mesin       | 2.90 |
    07| 20151002 | Dina  | P      | Surabaya | Akuntansi          | 3.30 |
    08| 20154003 | Dinda | P      | Jakarta  | Teknik Informatika | 3.65 |
    09| 20154002 | Doni  | L      | Makassar | Teknik Informatika | 2.75 |
    10| 20154001 | Eko   | L      | Jakarta  | Teknik Informatika | 3.25 |
    11| 20153001 | Ida   | P      | Semarang | Sastra Inggris     | 3.50 |
    12| 20154004 | Iski  | P      | Semarang | Teknik Informatika | 3.80 |
    13| 20151003 | Naura | P      | Bandung  | Akuntansi          | 2.85 |
    14| 20151001 | Nurul | P      | Jakarta  | Akuntansi          | 3.10 |
    15| 20153002 | Soni  | L      | Surabaya | Sastra Inggris     | 3.30 |
    16+----------+-------+--------+----------+--------------------+------+
    1710 rows in set (0.01 sec)
  • Tampilkan semua data Mahasiswa dan urutkan berdasarkan nama (descending)

    01mysql> SELECT * FROM mahasiswa
    02    -> ORDER BY nama DESC;
    03+----------+-------+--------+----------+--------------------+------+
    04| npm      | nama  | gender | asal     | jurusan            | ipk  |
    05+----------+-------+--------+----------+--------------------+------+
    06| 20153002 | Soni  | L      | Surabaya | Sastra Inggris     | 3.30 |
    07| 20151001 | Nurul | P      | Jakarta  | Akuntansi          | 3.10 |
    08| 20151003 | Naura | P      | Bandung  | Akuntansi          | 2.85 |
    09| 20154004 | Iski  | P      | Semarang | Teknik Informatika | 3.80 |
    10| 20153001 | Ida   | P      | Semarang | Sastra Inggris     | 3.50 |
    11| 20154001 | Eko   | L      | Jakarta  | Teknik Informatika | 3.25 |
    12| 20154002 | Doni  | L      | Makassar | Teknik Informatika | 2.75 |
    13| 20154003 | Dinda | P      | Jakarta  | Teknik Informatika | 3.65 |
    14| 20151002 | Dina  | P      | Surabaya | Akuntansi          | 3.30 |
    15| 20152001 | Budi  | L      | Bandung  | Teknik Mesin       | 2.90 |
    16+----------+-------+--------+----------+--------------------+------+
    1710 rows in set (0.00 sec)
  • Tampilkan semua data Mahasiswa dan urutkan berdasarkan nama dan asal (ascending)

    01mysql> SELECT * FROM mahasiswa
    02    -> ORDER BY nama ASC, asal ASC;
    03+----------+-------+--------+----------+--------------------+------+
    04| npm      | nama  | gender | asal     | jurusan            | ipk  |
    05+----------+-------+--------+----------+--------------------+------+
    06| 20152001 | Budi  | L      | Bandung  | Teknik Mesin       | 2.90 |
    07| 20151002 | Dina  | P      | Surabaya | Akuntansi          | 3.30 |
    08| 20154003 | Dinda | P      | Jakarta  | Teknik Informatika | 3.65 |
    09| 20154002 | Doni  | L      | Makassar | Teknik Informatika | 2.75 |
    10| 20154001 | Eko   | L      | Jakarta  | Teknik Informatika | 3.25 |
    11| 20153001 | Ida   | P      | Semarang | Sastra Inggris     | 3.50 |
    12| 20154004 | Iski  | P      | Semarang | Teknik Informatika | 3.80 |
    13| 20151003 | Naura | P      | Bandung  | Akuntansi          | 2.85 |
    14| 20151001 | Nurul | P      | Jakarta  | Akuntansi          | 3.10 |
    15| 20153002 | Soni  | L      | Surabaya | Sastra Inggris     | 3.30 |
    16+----------+-------+--------+----------+--------------------+------+
    1710 rows in set (0.00 sec)
  • Tampilkan semua data Mahasiswa dan urutkan berdasarkan nama (ascending), dan Jurusan (descending)

    01mysql> SELECT * FROM mahasiswa
    02    -> ORDER BY nama ASC, jurusan DESC;
    03+----------+-------+--------+----------+--------------------+------+
    04| npm      | nama  | gender | asal     | jurusan            | ipk  |
    05+----------+-------+--------+----------+--------------------+------+
    06| 20152001 | Budi  | L      | Bandung  | Teknik Mesin       | 2.90 |
    07| 20151002 | Dina  | P      | Surabaya | Akuntansi          | 3.30 |
    08| 20154003 | Dinda | P      | Jakarta  | Teknik Informatika | 3.65 |
    09| 20154002 | Doni  | L      | Makassar | Teknik Informatika | 2.75 |
    10| 20154001 | Eko   | L      | Jakarta  | Teknik Informatika | 3.25 |
    11| 20153001 | Ida   | P      | Semarang | Sastra Inggris     | 3.50 |
    12| 20154004 | Iski  | P      | Semarang | Teknik Informatika | 3.80 |
    13| 20151003 | Naura | P      | Bandung  | Akuntansi          | 2.85 |
    14| 20151001 | Nurul | P      | Jakarta  | Akuntansi          | 3.10 |
    15| 20153002 | Soni  | L      | Surabaya | Sastra Inggris     | 3.30 |
    16+----------+-------+--------+----------+--------------------+------+
    1710 rows in set (0.00 sec)
  • Tampilkan semua data Mahasiswa dan urutkan berdasarkan posisi kolom ke-6 (IPK) secara ascending.

    01mysql> SELECT * FROM mahasiswa
    02    -> ORDER BY 6 ASC;
    03+----------+-------+--------+----------+--------------------+------+
    04| npm      | nama  | gender | asal     | jurusan            | ipk  |
    05+----------+-------+--------+----------+--------------------+------+
    06| 20154002 | Doni  | L      | Makassar | Teknik Informatika | 2.75 |
    07| 20151003 | Naura | P      | Bandung  | Akuntansi          | 2.85 |
    08| 20152001 | Budi  | L      | Bandung  | Teknik Mesin       | 2.90 |
    09| 20151001 | Nurul | P      | Jakarta  | Akuntansi          | 3.10 |
    10| 20154001 | Eko   | L      | Jakarta  | Teknik Informatika | 3.25 |
    11| 20153002 | Soni  | L      | Surabaya | Sastra Inggris     | 3.30 |
    12| 20151002 | Dina  | P      | Surabaya | Akuntansi          | 3.30 |
    13| 20153001 | Ida   | P      | Semarang | Sastra Inggris     | 3.50 |
    14| 20154003 | Dinda | P      | Jakarta  | Teknik Informatika | 3.65 |
    15| 20154004 | Iski  | P      | Semarang | Teknik Informatika | 3.80 |
    16+----------+-------+--------+----------+--------------------+------+
    1710 rows in set (0.00 sec)

Contoh Kasus #1. Tampilkan rata-rata nilai dari seluruh mahasiswa.

Untuk menyelesaikannya tidak perlu menggunakan GROUP BY karena yang diminta adalah seluruh mahasiswa. Untuk mendapatkan rata-rata nilai, kita dapat menggunakan fungsi AVG(). Berikut ini query untuk kasus tersebut.
SELECT AVG(nilai) as rata_rata FROM nilai;
Berikut ini hasil dari query di atas.
+-----------+
| rata_rata |
+-----------+
| 79.6800   |
+-----------+
Contoh Kasus #2. Tampilkan rata-rata nilai untuk setiap mahasiswa
Karena yang diminta adalah rata-rata untuk setiap mahasiswa, maka kita harus menggunakan GROUP BY untuk mengelompokkan rata-rata berdasarkan field tertentu. Query dan hasilnya kurang lebih sebagai berikut:
SELECT nim, nama, AVG(nilai) as rata_rata FROM nilai GROUP BY nim;
+------------+------+-----------+
| nim        | nama | rata_rata |
+------------+------+-----------+
| 0911500101 | ADI  | 82.0000   |
| 0911500102 | IDA  | 81.0000   |
| 0911500103 | EDI  | 80.6000   |
| 0911500104 | INA  | 70.4000   |
| 0911500105 | ANI  | 84.4000   |
+------------+------+-----------+
Contoh Kasus #3. Tampilkan nilai terbesar dan terkecil untuk setiap mahasiswa.
Query untuk menyelesaikan kasus ketiga di atas sebenarnya sama saja dengan yang sebelumnya. Perbedaannya hanya pada fungsi agregat yang digunakan untuk menampilkan nilai terbesar dan terkecil yaitu MAX() dan MIN(). Berikut ini query dan hasil query-nya.
SELECT nim, nama, MAX(nilai) as terbesar, MIN(nilai) as terkecil FROM nilai GROUP BY nim;
+------------+------+----------+----------+
| nim        | nama | terbesar | terkecil |
+------------+------+----------+----------+
| 0911500101 | ADI  | 90       | 65       |
| 0911500102 | IDA  | 90       | 70       |
| 0911500103 | EDI  | 88       | 60       |
| 0911500104 | INA  | 80       | 50       |
| 0911500105 | ANI  | 92       | 68       |
+------------+------+----------+----------+
Contoh Kasus #4. Tampilkan rata-rata nilai yang didapat mahasiswa untuk setiap matakuliah
Cukup jelas bahwa pada kasus ini, mirip dengan kasus kedua di atas, namun pengelompokkan data berdasarkan matakuliah, bukan berdasarkan mahasiswa. Querynya kurang lebih sebagai berikut:
SELECT matkul, AVG(nilai) as rata_rata FROM nilai GROUP BY matkul;
+-----------------+-----------+
| matkul          | rata_rata |
+-----------------+-----------+
| ALGORITMA       | 84.4000   |
| KALKULUS        | 62.6000   |
| PBO             | 82.0000   |
| PEMROGRAMAN WEB | 86.4000   |
| PTI             | 83.0000   |
+-----------------+-----------+
Contoh Kasus #5. Tampilkan rata-rata nilai untuk setiap mahasiswa, yang rata-rata nilai lebih besar dari 80
Perhatikan kembali kasus kedua di atas. Hasil query menunjukkan bahwa untuk setiap mahasiswa akan ditampilkan nilai rata-rata yang diperoleh. Nah pada kasus ke-5 ini yang ingin ditampilkan adalah hanya mahasiswa yang nilainya lebih dari 80. Artinya untuk mahasiswa atas nama “INA” yang mendapat nilai rata-rata 70.4 tidak boleh ditampilkan. Solusinya adalah dengan menambahkan kondisi pada query kasus kedua diatas. Nah, jika kondisi suatu query terkait dengan fungsi agregat, maka kita tidak bisa menggunakan kondisi WHERE. Penggunaan WHERE pada fungsi agregat akan menyebabkan error.
Cobalah query berikut ini.
SELECT nim, nama, AVG(nilai) as rata_rata FROM nilai GROUP BY nim WHERE AVG(nilai)>80;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE AVG(nilai)>80' at line 1
Lalu bagaimana solusinya jika tidak bisa menggunakan WHERE ? MySQL (dan juga database yang lainnya) memiliki struktur kondisi khusus terkait fungsi agregat yaitu HAVING. Jika query diatas diperbaiki dan menggunakan HAVING, maka hasilnya sebagai berikut:
SELECT nim, nama, AVG(nilai) as rata_rata FROM nilai GROUP BY nim HAVING AVG(nilai)>80;
+------------+------+-----------+
| nim        | nama | rata_rata |
+------------+------+-----------+
| 0911500101 | ADI  | 82.0000   |
| 0911500102 | IDA  | 81.0000   |
| 0911500103 | EDI  | 80.6000   |
| 0911500105 | ANI  | 84.4000   |
+------------+------+-----------+

 GROUP BY Syntax

Sekarang kita sudah tahu apa itu klausa GROUP by, mari kita lihat sintaks untuk sebuah grup dasar dengan query.

SELECT statements... GROUP BY column_name1[,column_name2,...] [HAVING condition];
"Pernyataan SELECT ..." adalah kueri perintah SQL SELECT standar.
"GROUP BY column_name1" adalah klausa yang melakukan pengelompokan berdasarkan pada column_name1.
"[, column_name2, ...]" adalah opsional; mewakili nama kolom lain ketika pengelompokan dilakukan pada lebih dari satu kolom.
  "[Kondisi HAVING]" adalah opsional; ini digunakan untuk membatasi baris yang dipengaruhi oleh klausa GROUP BY. Ini mirip dengan klausa WHERE.
Pengelompokan menggunakan Kolom Tunggal

SELECT `gender` FROM `members` ;




gender


Female


Female


Male


Female


Male


Male


Male


Male


Male

Untuk membantu memahami efek klausa Group By, mari kita jalankan kueri sederhana yang mengembalikan semua entri gender dari tabel anggota.

Misalkan kita ingin mendapatkan nilai unik untuk jenis kelamin. Kami dapat menggunakan kueri berikut -

SELECT `gender` FROM `members` GROUP BY `gender`;

Menjalankan skrip di atas di meja kerja MySQL terhadap Myflixdb memberi kita hasil sebagai berikut.


gender
Female
Male

Perhatikan hanya dua hasil yang telah dikembalikan. Ini karena kami hanya memiliki dua jenis kelamin Pria dan Wanita. Klausa GROUP BY mengelompokkan semua anggota "Pria" bersama-sama dan hanya mengembalikan satu baris untuk itu. Itu melakukan hal yang sama dengan anggota "Perempuan".

Pengelompokan menggunakan banyak kolom
Misalkan kita ingin mendapatkan daftar movie_film dan tahun terkait di mana mereka dirilis.

Mari kita amati hasil dari query sederhana ini

SELECT `category_id`,`year_released` FROM `movies` ;




category_idyear_released


12011


22008


NULL2008


NULL2010


82007


62007


62007


82005


NULL2012


71920


8NULL


81920

Hasil di atas memiliki banyak duplikat.

Mari kita jalankan permintaan yang sama menggunakan grup dengan -

SELECT `category_id`,`year_released` FROM `movies` GROUP BY `category_id`,`year_released`;

Menjalankan skrip di atas di meja kerja MySQL terhadap myflixdb memberi kita hasil berikut yang ditunjukkan di bawah ini.

category_idyear_released
NULL2008
NULL2010
NULL2012
12011
22008
62007
71920
81920
82005
82007

Klausa GROUP BY beroperasi pada id kategori dan tahun yang dirilis untuk mengidentifikasi baris unik dalam contoh kami di atas.

Jika id kategori sama tetapi tahun yang dirilis berbeda, maka baris dianggap unik. Jika id kategori dan tahun yang dirilis sama untuk lebih dari satu baris, maka dianggap sebagai duplikat dan hanya satu baris ditampilkan.

Fungsi pengelompokan dan agregat
Misalkan kita ingin jumlah total pria dan wanita di database kami. Kita dapat menggunakan skrip berikut di bawah ini untuk melakukannya.

SELECT `gender`,COUNT(`membership_number`)  FROM `members` GROUP BY `gender`;

Menjalankan skrip di atas di meja kerja MySQL terhadap myflixdb memberi kita hasil sebagai berikut.

genderCOUNT('membership_number')
Female3
Male5

Hasil yang ditunjukkan di bawah ini dikelompokkan berdasarkan setiap nilai gender unik yang diposkan dan jumlah baris yang dikelompokkan dihitung menggunakan fungsi agregat COUNT.

Membatasi hasil kueri menggunakan klausa HAVING
Tidak selalu kami ingin melakukan pengelompokan pada semua data dalam tabel yang diberikan. Akan ada saat-saat ketika kami ingin membatasi hasil kami dengan kriteria tertentu. Dalam kasus seperti itu, kita dapat menggunakan klausa HAVING

Misalkan kita ingin tahu semua tahun rilis untuk id kategori film 8. Kami akan menggunakan skrip berikut untuk mencapai hasil kami.

SELECT * FROM `movies` GROUP BY `category_id`,`year_released` HAVING `category_id` = 8;
Menjalankan skrip di atas di meja kerja MySQL terhadap Myflixdb memberi kita hasil berikut yang ditunjukkan di bawah ini.

Perhatikan hanya film dengan id kategori 8 yang terpengaruh oleh klausa GROUP BY kami.

movie_idtitledirectoryear_releasedcategory_id
9Honey moonersJohn Schultz20058
5Daddy's Little GirlsNULL20078

Ringkasan
Klausa GROUP BY digunakan untuk mengelompokkan baris dengan nilai yang sama.
Klausa GROUP BY digunakan bersama dengan pernyataan SQL SELECT.
Pernyataan SELECT yang digunakan dalam klausa GROUP BY hanya dapat digunakan berisi nama kolom, fungsi agregat, konstanta, dan ekspresi.
Klausa HAVING digunakan untuk membatasi hasil yang dikembalikan oleh klausa GROUP BY.
Diposting oleh di Tidak ada komentar:
Langganan: Postingan (Atom)