Apa itu aggregate function di mysql?

Home
MySQL Home
▼MySQL Aggregate Functions and Grouping
Aggregate Functions and Grouping
AVG()
BIT_AND()
BIT_OR()
BIT_XOR()
COUNT()
GROUP_CONCAT()
MAX()
MIN()
STD()
STDDEV_POP()
STDDEV_SAMP()
STDDEV()
SUM()
VAR_POP()
VAR_SAMP()
VARIANCE()

Table of Contents Show

aggregate functions
Using the MySQL AVG() Function
Using the MySQL COUNT() Function
Using the MySQL MAX() Function
Using the MySQL MIN() Function
Using the SUM() Function
Using Multiple Aggregate Functions

MySQL aggregate functions and groupingLast update on August 19 2022 21:51:23 (UTC/GMT +8 hours)

aggregate functions

MySQL aggregate functions retrieve a single value after performing a calculation on a set of values.

In general, aggregate functions ignore null values.

Often, aggregate functions are accompanied by the GROUP BY clause of the SELECT statement.

List of MySQL aggregate functions and a hint of what they do

AVG()

MySQL AVG() retrieves the average value of the argument.

BIT_AND()

MySQL BIT_AND() bitwise and.

BIT_OR()

MySQL BIT_OR() retrieves bitwise or.

BIT_XOR()

MySQL BIT_OR() retrieves bitwise xor.

COUNT(DISTINCT)

MySQL COUNT(DISTINCT) retrieves the count of a number of different values.

COUNT()

MySQL COUNT() retrieves a count of the number of rows returned.

GROUP_CONCAT()

MySQL GROUP_CONCAT() retrieves a concatenated string.

MAX()

MySQL MAX() retrieves the maximum value.

MIN()

MySQL BIT_OR() retrieves the minimum value.

STD()

MySQL MIN()retrieves the population standard deviation.

STDDEV_POP()

MySQL BIT_OR() retrieves the population standard deviation.

STDDEV_SAMP()

MySQL STDDEV_POP() retrieves the sample standard deviation.

STDDEV()

MySQL STDDEV() retrieves the population standard deviation.

SUM()

MySQL SUM() retrieves the sum.

VAR_POP()

MySQL VAR_POP() the population standard variance.

VAR_SAMP()

MySQL VAR_POP() the sample variance.

VARIANCE()

MySQL VAR_POP()the population standard variance.

Previous: MySQL UNION
Next: AVG()

Weekly Trends
Java Basic Programming Exercises
SQL Subqueries
Adventureworks Database Exercises
C# Sharp Basic Exercises
SQL COUNT() with distinct
JavaScript String Exercises
JavaScript HTML Form Validation
Java Collection Exercises
SQL COUNT() function
SQL Inner Join
JavaScript functions Exercises
Python Tutorial
Python Array Exercises
SQL Cross Join
C# Sharp Array Exercises

CC BY-NC-ND International 4.0: This work is released under a CC BY-NC-ND International 4.0 license, which means that you are free to do with it as you please as long as you (1) properly attribute it, (2) do not use it for commercial gain, and (3) do not create derivative works.

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 |
+-----------+----------------------------+-----------------------------+------------+

Feel free to create a similar table, or just follow along with the examples to get an idea of how to use these functions.

Using the 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)

Using the 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)

Using the 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)