Aggregate Functions

What makes a function "aggregate"

The opposite of single row: instead of returning one result per row, an aggregate function collapses many rows down into a single result. SELECT salary FROM employee returns 8 rows, one per employee, SELECT SUM(salary) FROM employee returns exactly 1, the total across all of them.

SUM()

select sum(salary) from employee;

+-------------+
| sum(salary) |
+-------------+
|   531000.00 |
+-------------+
1 row in set (0.00 sec)

SUM(column) adds up every value in column across all matching rows, all 8 salaries added together, 531000.00. Every row collapses into that one number.

AVG()

select avg(salary) from employee;

+--------------+
| avg(salary)  |
+--------------+
| 66375.000000 |
+--------------+
1 row in set (0.00 sec)

AVG(column) returns the mean, the same 531000.00 total from SUM divided by the 8 rows it came from, 66375. The extra trailing zeros (.000000) aren't a mistake, AVG returns a decimal with more precision than the source column had, since an average often doesn't land on a clean number.

MIN() / MAX()

select min(salary) from employee;

+-------------+
| min(salary) |
+-------------+
|    31000.00 |
+-------------+
1 row in set (0.00 sec)

select max(salary) from employee;

+-------------+
| max(salary) |
+-------------+
|   100000.00 |
+-------------+
1 row in set (0.00 sec)

MIN(column) and MAX(column) return the smallest and largest values in column, same 8 rows collapsed down to a single number each, 31000.00 is the lowest salary in employee, 100000.00 the highest. Neither is limited to numbers either, MIN/MAX work on dates and text too, earliest/latest date, or first/last alphabetically.

COUNT()

select count(*) from employee;

+----------+
| count(*) |
+----------+
|       10 |
+----------+
1 row in set (0.00 sec)

select count(name) from employee;

+-------------+
| count(name) |
+-------------+
|          10 |
+-------------+
1 row in set (0.01 sec)

select count(mob) from employee;

+------------+
| count(mob) |
+------------+
|          9 |
+------------+
1 row in set (0.00 sec)

select count(doj) from employee;

+------------+
| count(doj) |
+------------+
|          8 |
+------------+
1 row in set (0.00 sec)

COUNT(*) counts rows, every single one, regardless of what's in them, 10 matches the table's current row count exactly. COUNT(column) counts something different: how many rows have a non-NULL value in that specific column, skipping any row where it's NULL.

• COUNT(name) is also 10, name is NOT NULL, so every row has a value in it, same count as COUNT(*).
• COUNT(mob) drops to 9, one row has a NULL in mob (nothing was supplied for it on insert), that row doesn't get counted.
• COUNT(doj) drops further to 8, more rows missing a doj value.

COUNT(*) and COUNT(some NOT NULL column) will always agree. The moment COUNT is pointed at a column that actually allows NULL, it stops being "how many rows" and becomes "how many rows actually have data here", the gap between that number and COUNT(*) is exactly how many NULLs exist in that column.

GROUP BY: aggregating per group instead of overall

Every aggregate example so far collapsed the whole table into one row. GROUP BY changes that: it collapses the table into one row per distinct value of whatever column it names instead.

select desig, sum(salary) from employee group by desig;

+---------------+-------------+
| desig         | sum(salary) |
+---------------+-------------+
| Manager       |    50000.00 |
| Clerk         |   201000.00 |
| Asst. Clerk   |    61000.00 |
| MTS           |   100000.00 |
| Peon          |   140345.00 |
| Co-Manager    |    75000.00 |
| Asst. Manager |    44000.00 |
+---------------+-------------+
7 rows in set (0.00 sec)

GROUP BY desig buckets every row by its desig first, then SUM(salary) runs separately within each bucket instead of across the whole table, one total per designation instead of one grand total. 7 distinct designations, 7 rows back, 'Clerk''s 201000.00 is the sum of just the 3 clerks, not everyone.

HAVING: filtering on the grouped result

select desig, sum(salary) from employee group by desig having desig="clerk";

+-------+-------------+
| desig | sum(salary) |
+-------+-------------+
| Clerk |   201000.00 |
+-------+-------------+
1 row in set (0.00 sec)

HAVING filters the groups GROUP BY produces, the same role WHERE plays for individual rows, but applied after grouping instead of before. That distinction is why HAVING exists as a separate keyword at all, WHERE runs too early to filter on anything grouping or aggregation produced, HAVING is what runs after.

(This particular example filters on desig itself, the grouping column, not on the aggregate SUM(salary), so WHERE desig = "clerk" before the GROUP BY would land on the same single row. HAVING earns its keep specifically when the filter needs the aggregate result, something like HAVING SUM(salary) > 100000, a condition that doesn't exist yet at the point WHERE runs.)

HAVING isn't tied to any one aggregate function either, it works the same with AVG:

select desig, avg(salary) from employee group by desig having desig="clerk";

+-------+--------------+
| desig | avg(salary)  |
+-------+--------------+
| Clerk | 67000.000000 |
+-------+--------------+
1 row in set (0.00 sec)

Same grouping, same HAVING clause, just AVG(salary) instead of SUM(salary), 67000 being the average across the same 3 clerks that summed to 201000.

COUNT(*) groups just as naturally, answering "how many rows per group" instead of "what's the total per group":

select desig, count(*) from employee group by desig;

+---------------+----------+
| desig         | count(*) |
+---------------+----------+
| Manager       |        1 |
| Clerk         |        3 |
| Asst. Clerk   |        1 |
| MTS           |        1 |
| Peon          |        2 |
| Co-Manager    |        1 |
| Asst. Manager |        1 |
+---------------+----------+
7 rows in set (0.00 sec)

Same shape as the SUM/AVG examples above, one row per distinct desig, but the aggregate this time is a headcount, 'Clerk' has 3 employees, matching the 3 rows that summed to 201000.00 earlier. Any aggregate function slots into this same GROUP BY pattern the same way, SUM, AVG, COUNT, MIN, MAX, all work per group exactly like they work over the whole table.