Contoh Penggunaan COUNT, MIN dan MAX dalam SQL, studi kasus database MySQL. untuk database lain sintaks sama saja.

aggregate.sql

D:\xampp\mysql\bin>mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.16 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> ----------------------------------------------------
mysql> -- pilih database
mysql> ----------------------------------------------------
mysql> use test ;
Database changed

mysql> ----------------------------------------------------
mysql> -- buat table dengan contoh data
mysql> ----------------------------------------------------
mysql> create table data_cuti as
    -> select 1 no, 'budi' nama, 1 bulan union all
    -> select 2 no, 'budi' nama, 2 bulan union all
    -> select 3 no, 'budi' nama, 3 bulan union all
    -> select 4 no, 'budi' nama, 4 bulan union all
    -> select 5 no, 'andi' nama, 9 bulan ;
Query OK, 5 rows affected (0.14 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> ----------------------------------------------------
mysql> -- lihat isi table dan contoh datanya
mysql> ----------------------------------------------------
mysql> select * from data_cuti ;
+----+------+-------+
| no | nama | bulan |
+----+------+-------+
|  1 | budi |     1 |
|  2 | budi |     2 |
|  3 | budi |     3 |
|  4 | budi |     4 |
|  5 | andi |     9 |
+----+------+-------+
5 rows in set (0.00 sec)

mysql> ----------------------------------------------------
mysql> -- query untuk perhitungannya
mysql> -- count => untuk menghitung jumlah row
mysql> -- min => untuk mencari nilai paling kecil
mysql> -- max => untuk mencari nilai paling besar
mysql> ----------------------------------------------------
mysql> select nama
    -> , count(*) as jumlah_bulan_cuti
    -> , min(bulan) as awal_bulan_cuti
    -> , max(bulan) as akhir_bulan_cuti
    -> from data_cuti
    -> group by nama ;
+------+-------------------+-----------------+------------------+
| nama | jumlah_bulan_cuti | awal_bulan_cuti | akhir_bulan_cuti |
+------+-------------------+-----------------+------------------+
| andi |                 1 |               9 |                9 |
| budi |                 4 |               1 |                4 |
+------+-------------------+-----------------+------------------+
2 rows in set (0.00 sec)

mysql> ----------------------------------------------------
mysql> -- YAAYYY!!! Berhasil
mysql> ----------------------------------------------------
mysql>