MySQL Aggregate Functions

What is aggregate function in SQL?

• The aggregate function uses to calculate on a set of values in a column and return a single value.
• In SQL, aggregate function often requires to add the GROUP BY statement.
• The HAVING clause is similar to the WHERE clause, and the HAVING clause is normally used along with the GROUP BY clause.
• The HAVING clause and GROUP BY clause use in aggregate function.
• The SQL aggregate functions: SUM, AVG, COUNT, MAX, MIN.

Create TABLE and INSERT Records

mysql> CREATE TABLE my_letter_mark
    -> (
    -> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> score DOUBLE NOT NULL,
    -> letter CHAR NOT NULL);
Query OK, 0 rows affected (0.23 sec)

mysql> INSERT INTO my_letter_mark VALUES(null,30,'a'),
   -> (null,40,'a'),
   -> (null,49,'a'),
   -> (null,39,'b'),
   -> (null,31,'b'),
   -> (null,43,'b'),
   -> (null,12,'c'),
   -> (null,35,'b');
Query OK, 8 rows affected (0.03 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM my_letter_mark;
+----+-------+--------+
| id | score | letter |
+----+-------+--------+
|  1 |    30 | a      |
|  2 |    40 | a      |
|  3 |    49 | a      |
|  4 |    39 | b      |
|  5 |    31 | b      |
|  6 |    43 | b      |
|  7 |    12 | c      |
|  8 |    35 | b      |
+----+-------+--------+
8 rows in set (0.00 sec)

MySQL COUNT

• The aggregate function COUNT is to count all the items.
• It is answering HOW MANY!

mysql> SELECT COUNT(id) total FROM my_letter_mark ;
+-------+
| total |
+-------+
|     8 |
+-------+
1 row in set (0.00 sec)

MySQL Sum

• The aggregate function SUM is to total all the items.

mysql> SELECT SUM(score) FROM my_letter_mark;
+------------+
| SUM(score) |
+------------+
|        279 |
+------------+
1 row in set (0.00 sec)

MySQL Avg

• The aggregate function AVG is to get the average of the items.

mysql> SELECT AVG(score) FROM my_letter_mark;
+------------+
| AVG(score) |
+------------+
|     34.875 |
+------------+
1 row in set (0.00 sec)

MySQL Max

• The aggregate function MAX is to return the largest item.
• It answers which one is the largest item in the column.

mysql> SELECT MAX(score) FROM my_letter_mark;
+------------+
| MAX(score) |
+------------+
|         49 |
+------------+
1 row in set (0.00 sec)

MySQL Min

• The aggregate function MIN is opposite from MAX.
• The MIN returns the smallest item.

mysql> SELECT MIN(score) FROM my_letter_mark;
+------------+
| MIN(score) |
+------------+
|         12 |
+------------+
1 row in set (0.00 sec)

MySQL GROUP BY

• Now, it is time to use the aggregate function with the GROUP BY clause.
• The GROUP BY clause is used in the aggregate function to group a particular item.
• In this SQL query, we group the return query base on the letter.
• We count the total of each letter which is based on the letter.
• We count how many of each letter existed on the table.

mysql> SELECT COUNT(id) total,letter FROM my_letter_mark GROUP BY letter;
+-------+--------+
| total | letter |
+-------+--------+
|     3 | a      |
|     4 | b      |
|     1 | c      |
+-------+--------+
3 rows in set (0.00 sec)

MySQL HAVING

• The HAVING clause is similar with the WHERE clause.
• The HAVING clause is used within the aggregate function.
• It uses to filter the return query for the GROUP BY clause.

mysql> SELECT COUNT(id) total,letter 
  -> FROM my_letter_mark 
  -> GROUP BY letter HAVING total >=3;
+-------+--------+
| total | letter |
+-------+--------+
|     3 | a      |
|     4 | b      |
+-------+--------+
2 rows in set (0.00 sec)

SUM All The Score According To The Letter

• In this query, the return query sum of all the score based on each letter.

mysql> SELECT SUM(score),letter 
    -> FROM my_letter_mark GROUP BY letter;
+------------+--------+
| SUM(score) | letter |
+------------+--------+
|        119 | a      |
|        148 | b      |
|         12 | c      |
+------------+--------+
3 rows in set (0.00 sec)

LIST ONLY TOTAL SCORE THAT IS EQUAL TO 12

mysql> SELECT SUM(score) total,letter 
    -> FROM my_letter_mark GROUP BY letter HAVING total=12; 
+-------+--------+
| total | letter |
+-------+--------+
|    12 | c      |
+-------+--------+
1 row in set (0.00 sec)

List Average Score for each letter

mysql> SELECT AVG(score) avg,letter 
   -> FROM my_letter_mark GROUP BY letter ;
+------------------+--------+
| avg              | letter |
+------------------+--------+
| 39.6666666666667 | a      |
|               37 | b      |
|               12 | c      |
+------------------+--------+
3 rows in set (0.27 sec)

List the smallest score for each letter

mysql> SELECT MIN(score) as smallest, letter 
  -> FROM my_letter_mark GROUP BY letter;
+----------+--------+
| smallest | letter |
+----------+--------+
|       30 | a      |
|       31 | b      |
|       12 | c      |
+----------+--------+
3 rows in set (0.00 sec)