Cara Menggunakan Aggregate Function pada MySQL

Oleh Indrawati Desember 07, 2022 Posting Komentar

Terkadang saat bekerja dengan data yang disimpan dalam tabel database MySQL, kami tidak hanya menggunakan perinta CRUD saja, tetapi terkadang kita membutuhkan suatu data yang berkaitan dengan hasil berupa numeric.

Sebagai contoh,terkadang kita ingin mengetahui berapa jumlah data, berapa nilai terbesar atau terkecil atau mungkin ingin mengetahui berapa banyak baris dalam sebuah tabel. caranya kita dapat menggunakan fungsi agregat MySQL.

The MySQL Aggregate Functions

MySQL supports the following aggregate functions:

AVG(),Returns the average of the values in the selected column
COUNT(),Returns the number of rows returned for a selection
MAX(),Returns the maximum value for a column
MIN(),Returns the minimum value of a column
SUM(),Returns the sum of the values in a specified column

Menggunakan Aggregate Functions

CREATE TABLE `products` (
  `prod_code` varchar(100) NOT NULL,
  `prod_name` varchar(250) NOT NULL,
  `prod_desc` tinytext NOT NULL,
  `prod_price` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Tambahkan data dengan menjalankan perintah berikut :

INSERT INTO `products` (`prod_code`, `prod_name`, `prod_desc`, `prod_price`) VALUES
('1', 'WildTech 250Gb 1700 ', 'SATA Disk Drive  ', 120),
('2', 'Moto Razr ', 'Mobile Phone  ', 200),
('3', 'Microsoft 10-20 Keyboard', 'Ergonmoc Keyboard', 49),
('4', 'EasyTech Mouse 7632', 'Cordless Mouse ', 49),
('5', 'Dell XPS 400  ', 'Desktop PC', 999),
('6', 'Buffalo AirStation Turbo G ', 'Wireless Ethernet Bridge ', 60),
('7', 'Apple iPod Touch   ', 'Portable Music/Movie Player', 199),
('8', 'Apple iPhone 8Gb ', 'Smart Phone', 399);

For the purposes of demonstrating the aggregate functions in action we will use a table with the following data:

+-----------+----------------------------+-----------------------------+------------+
| prod_code | prod_name                  | prod_desc                   | prod_price |
+-----------+----------------------------+-----------------------------+------------+
|         1 | WildTech 250Gb 1700        | SATA Disk Drive             |        120 |
|         2 | Moto Razr                  | Mobile Phone                |        200 |
|         3 | Microsoft 10-20 Keyboard   | Ergonmoc Keyboard           |         49 |
|         4 | EasyTech Mouse 7632        | Cordless Mouse              |         49 |
|         5 | Dell XPS 400               | Desktop PC                  |        999 |
|         6 | Buffalo AirStation Turbo G | Wireless Ethernet Bridge    |         60 |
|         7 | Apple iPod Touch           | Portable Music/Movie Player |        199 |
|         8 | Apple iPhone 8Gb           | Smart Phone                 |        399 |
+-----------+----------------------------+-----------------------------+------------+

Anda dapat membuat tabel serupa untuk mempraktekan perintah dari Aggregate Function pada MySQL

Cara Menggunakan MySQL AVG() Function

The AVG() function adds together all the values for a specified column in a SELECT statement and divides it by the number of rows to arrive at an average value. The result can then be assigned to an alias using the AS clause. For example, to find the average price of the products in our database, and assign the result to an alias named price_avg:

mysql> SELECT AVG(prod_price) AS price_ag FROM products;
+----------+
| price_ag |
+----------+
|  259.375 |
+----------+
1 row in set (0.00 sec)

We can also be selective about the rows used in the average calculation by using the WHERE clause:

mysql> SELECT AVG(prod_price) AS price_avg FROM products WHERE prod_price BETWEEN 10 and 199;
+-----------+
| price_avg |
+-----------+
|     95.4  |
+-----------+
1 row in set (0.00 sec)

Cara Menggunakan MySQL COUNT() Function

The MySQL COUNT() function adds the number of rows that match the filter criteria specified in a SELECT statement. For example, to count the number of rows with a price in our sample table:

mysql> SELECT COUNT(*) FROM products;
+----------+
| price_ag |
+----------+
|        8 |
+----------+
1 row in set (0.00 sec)

Similarly, we can restrict our criteria to list the number of products beneath a specific price threshold:

mysql> SELECT COUNT(prod_price) AS low_price_items FROM products WHERE prod_price < 200;
+-----------------+
| low_price_items |
+-----------------+
|               5 |
+-----------------+
1 row in set (0.00 sec)

Cara Menggunakan MySQL MAX() Function

The MAX() function returns data from the row in which the specified column contains the highest value. For example we can find the most expensive product in our database table:

mysql> SELECT MAX(prod_price) AS max_price FROM products;
+-----------+
| max_price |
+-----------+
|       999 |
+-----------+
1 row in set (0.00 sec)

Using the MySQL MIN() Function

The MIN() function performs the opposite task to the MAX() function in that it returns data from the row containing the lowest value in the specified column. For example, to find the least expensive item in our table:

mysql> SELECT MIN(prod_price) AS min_price FROM products;
+-----------+
| max_price |
+-----------+
|        49 |
+-----------+
1 row in set (0.00 sec)

Using the SUM() Function

The SUM() function returns the total of all the values in a specified column. Therefore, to get the total value of every item in the table:

mysql> SELECT SUM(prod_price) AS total_price FROM products;
+-----------+
| max_price |
+-----------+
|      2075 |
+-----------+
1 row in set (0.00 sec)

Using Multiple Aggregate Functions

SELECT statements are not restricted to a single aggregate function. It is perfectly valid to include calls to multiple functions, for example:

mysql> SELECT MAX(prod_price) AS max_price, MIN(prod_price) AS max_price FROM products;
+-----------+-----------+
| max_price | max_price |
+-----------+-----------+
|       999 |        49 |
+-----------+-----------+
1 row in set (0.00 sec)

Contoh Aplikasi