Grouping and Aggregating Records in MySQL

In SQL, data aggregation refers to the process of grouping data from multiple rows based on certain criteria and summarizing it into a single result using aggregate functions and clauses.

Among other things, it allows to:

• Summarize large sets of data to produce consolidated results such as totals, averages, counts, etc.
• Extract statistical summaries to identify patterns and distributions within the data.
• Reduce the volume of data for easier analysis and reporting.

Some use cases may include:

• Calculating total sales by product category.
• Finding average scores of students per subject.
• Counting the number of orders placed by customers.

Aggregate functions

In SQL, aggregate functions are functions used to perform calculations on a set of rows and return a single value as the result.

These functions are typically used in conjunction with SELECT statements, often in combination with the GROUP BY and HAVING clauses.

Counting the number of rows

To count the total number of rows of a table, you can use the COUNT function with the * wildcard as argument:

SELECT COUNT(*) FROM table_name;

To count the total number of rows of a non-null column, you can use the COUNT function with the name of column as argument:

SELECT COUNT(column_name) FROM table_name;

Example

This SQL statement will count the number of rows in the employees table:

mysql> SELECT COUNT(*) FROM employees;
+----------+
| COUNT(*) |
+----------+
|       10 |
+----------+
1 row in set (0.02 sec)

Calculating the sum of a column

To calculate the sum of a column containing numeric values, you can use the SUM function with the name of the column as argument:

SELECT SUM(colum_name) FROM table_name;

Example

This SQL statement will calculate the sum of the salary column in all the rows of the employees table:

mysql> SELECT SUM(salary) FROM employees;
+-------------+
| SUM(salary) |
+-------------+
|      594000 |
+-------------+
1 row in set (0.00 sec)

Calculating the average of a column

To calculate the average of a column containing numeric values, you can use the AVG function with the name of the column as argument:

SELECT AVG(colum_name) FROM table_name;

Example

This SQL statement will calculate the average value of the salary column of all the rows in the employees table:

mysql> SELECT AVG(salary) FROM employees;
+--------------+
| AVG(salary)  |
+--------------+
| 59400.000000 |
+--------------+
1 row in set (0.00 sec)

Finding the minimum and maximum of a column

To find the minimum and maximum of a column containing numeric values, you can use the MIN and MAX functions with the name of the column as argument:

SELECT MIN(colum_name) FROM table_name;
SELECT MAX(colum_name) FROM table_name;

Example

This SQL statement will retrieve the biggest value of the salary column of all the rows in the employees table where the department column equals the string 'Engineering':

mysql> SELECT MAX(salary) FROM employees WHERE department = 'Engineering';
+-------------+
| MAX(salary) |
+-------------+
|       77000 |
+-------------+
1 row in set (0.00 sec)

Grouping rows with identical values

Aggregate functions alone don't usually provide enough context when trying to summarize multiple rows of data.

To improve summarized result sets, you can group rows together based on identical column values using the GROUP BY clause:

SELECT column_name, ..., aggregate_function(column_name)
FROM table_name
WHERE condition
GROUP BY column_name, ...;

Example

This SQL statement will retrieve the department column and aggregate the salary column using the AVG() function, then group the results by department:

mysql> SELECT department, AVG(salary) FROM employees GROUP BY department;
+-------------+-------------+
| department  | AVG(salary) |
+-------------+-------------+
| Sales       |  52333.3333 |
| Engineering |  74000.0000 |
| HR          |  46500.0000 |
| Marketing   |  61000.0000 |
+-------------+-------------+
4 rows in set (0.01 sec)

Filtering aggregated records

To filter aggregated records based on a condition, you can combine the GROUP BY clause with the HAVING clause:

SELECT column_name, ..., aggregate_function(column_name)
FROM table_name
WHERE condition
GROUP BY column_name, ...
HAVING condition;

Note: Unlike the WHERE clause, the HAVING clause is used to filter records after they've been aggregated.

Example

This SQL statement will retrieve the department column and aggregate the salary column using the AVG() function under a column named average_salary, group the results by the department column, and filter the results where the value of the average_salary column is greater than 60000:

mysql> SELECT department, AVG(salary) AS average_salary FROM employees GROUP BY department HAVING average_salary > 60000;
+-------------+----------------+
| department  | average_salary |
+-------------+----------------+
| Engineering |     74000.0000 |
| Marketing   |     61000.0000 |
+-------------+----------------+
2 rows in set (0.00 sec)

Generating subtotal values for aggregations

To automatically generate additional summary rows containing the subtotal and grand total values of the aggregated rows, you can combine the GROUP BY clause with the WITH ROLLUP clause:

SELECT column_name, ..., aggregate_function(column_name)
FROM table_name
WHERE condition
GROUP BY column_name, ...
WITH ROLLUP;

Example

This SQL statement will retrieve the department column and aggregate the salary column using the AVG() function, group the results by the department column, and add an additional row that shows the overall average of the salary column:

mysql> SELECT department, AVG(salary) AS average_salary FROM employees GROUP BY department WITH ROLLUP;
+-------------+----------------+
| department  | average_salary |
+-------------+----------------+
| Engineering |     74000.0000 |
| HR          |     46500.0000 |
| Marketing   |     61000.0000 |
| Sales       |     52333.3333 |
| NULL        |     59400.0000 |
+-------------+----------------+
5 rows in set (0.02 sec)

Summary

Here's a summary of what you've learned in this lesson:

• An aggregate function is used to perform calculations on a set of rows and return a single value as the result.
• The COUNT() function is used to count the number of rows.
• The SUM() function is used to calculate the sum of a column containing numeric values.
• The AVG() function is used to calculate the average of a column containing numeric values.
• The MIN() function is used to find the minimum of a column containing numeric values.
• The MAX() function is used to find the maximum of a column containing numeric values.
• The GROUP BY clause is used to group rows together based on identical column values.
• The HAVING clause is used to filter aggregated rows based on a condition.
• The WITH ROLLUP clause is used to generate subtotal and grand total values of aggregated rows.