Aggregation Queries ​

When we want to count the number of records in a table, like in the students table, we don't need to retrieve all rows and count them manually. Instead, SQL provides aggregation functions for efficient calculations, known as aggregation queries.

For instance, to count the total number of records in the students table, we can use the COUNT() function:

-- Using aggregation query:
SELECT COUNT(*) FROM students;

COUNT(*) returns the total number of rows. Although the result is a single number, it is still presented as a two-dimensional table with one row and one column. It’s advisable to assign an alias to the column for clarity:

-- Using aggregation query with an alias:
SELECT COUNT(*) AS num FROM students;

The COUNT(*) function is equivalent to COUNT(id). Additionally, aggregation queries can use WHERE conditions to count specific groups, such as the number of boys or girls:

-- Count boys:
SELECT COUNT(*) AS boys FROM students WHERE gender = 'M';

SQL provides several aggregation functions, including:

• SUM: Calculates the total value of a numeric column.
• AVG: Calculates the average of a numeric column.
• MAX: Finds the maximum value in a column.
• MIN: Finds the minimum value in a column.

For example, to calculate the average score of boys, we can use:

-- Average score of boys:
SELECT AVG(score) AS average FROM students WHERE gender = 'M';

Note that if the WHERE condition returns no matches, COUNT() will return 0, while SUM(), AVG(), MAX(), and MIN() will return NULL:

-- Average score where gender doesn't match:
SELECT AVG(score) AS average FROM students WHERE gender = 'X';

To calculate the total number of pages when displaying 3 records per page, we can use:

SELECT CEILING(COUNT(*) / 3.0) FROM students;  -- To get the total pages

Grouping ​

If we want to count the number of students in each class, we can use:

-- Count students in class 1:
SELECT COUNT(*) AS num FROM students WHERE class_id = 1;

However, to count students in all classes without modifying the WHERE condition multiple times, we can use the GROUP BY clause:

-- Group by class_id:
SELECT COUNT(*) AS num FROM students GROUP BY class_id;

This query will return a count of students for each class_id. To make the results clearer, we can include class_id in the output:

-- Group by class_id with class_id in the result:
SELECT class_id, COUNT(*) AS num FROM students GROUP BY class_id;

To analyze more categories, we can group by multiple columns, such as counting boys and girls in each class:

-- Group by class_id and gender:
SELECT class_id, gender, COUNT(*) AS num FROM students GROUP BY class_id, gender;

This will give results corresponding to the number of boys and girls in each class.

Exercises ​

1. To find the average score for each class:

SELECT class_id, AVG(score) AS average FROM students GROUP BY class_id;

2. To find the average score for boys and girls in each class:

SELECT class_id, gender, AVG(score) AS average FROM students GROUP BY class_id, gender;

Summary ​

Using SQL’s aggregation queries allows us to easily calculate totals, sums, averages, maximums, and minimums. Aggregation queries can be grouped using GROUP BY and can also include WHERE conditions for more specific filtering.