While cleaning up the report queries in my employee dashboard, I encountered aggregate functions again. Initially, the logic seemed simple: count the number of employees, calculate total salaries, average the results, and extract the highest and lowest values. However, as the data volume increased, some NULL values appeared in the tables, and I realized I needed to be more cautious than expected.
Aggregate functions are SQL functions I typically use when I need to summarize data rather than viewing details row by row. They process a group of rows and return a single result, making them ideal for reports, dashboards, and quick data inspections.
In MySQL, the ones I use most often are these:
COUNT()– counts rows or non-null values.SUM()– adds up numeric values.AVG()– calculates the average of numeric values.MAX()– returns the highest value.MIN()– returns the lowest value.
Setting Up the Example Table
For the examples below, I used a pretty standard employees table:
CREATE TABLE employees (
id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
department VARCHAR(50),
salary DECIMAL(10,2),
hire_date DATE
);This table was enough to reproduce the same kind of questions I had in the project: how many employees are there, what is the total payroll, which department is the most expensive, and so on.
Using COUNT()
COUNT() was the first function I checked because I just wanted a basic sanity check. In one of the early queries, I was comparing the UI total with the database total, and the numbers did not match. That turned out to be a simple filtering issue, but COUNT() made the mismatch obvious immediately.
The COUNT() function returns the number of rows that match a condition, or the total number of rows in a table.
Example 1: Count All Employees
SELECT COUNT(*) AS total_employees
FROM employees;Step-by-Step Analysis:
The COUNT(*) function counts the number of rows in the employees table.
The AS total_employees function gives the result a meaningful name.
It returns a number representing the total number of employees.
Example 2: Count Employees in a Department
SELECT COUNT(*) AS sales_count
FROM employees
WHERE department = 'Sales';Logic:
WHERE department = 'Sales' filters only Sales employees.COUNT(*) returns the number of employees in that department.
This one was useful when I wanted to confirm whether the department filter in the frontend matched the backend query. In practice, the mistake was not in COUNT(), but in the data itself: a few rows had slightly different department values, like extra spaces or inconsistent capitalization, which made the count look wrong until I checked the raw data.
Using SUM()
I usually move to SUM() when I need totals for finance, payroll, or any kind of grouped reporting. In one project review, the payroll total looked surprisingly low at first, and the issue was that one salary field had been left NULL, so the total was missing that row entirely. That kind of thing is easy to overlook if you do not check the raw table.
The SUM() function adds up numeric values in a column.
Example 1: Total Salaries
SELECT SUM(salary) AS total_salary
FROM employees;Step-by-Step Analysis:
SUM(salary) calculates the total of all salaries.
Useful for budget planning or payroll analysis.
Example 2: Sum by Department
SELECT department, SUM(salary) AS department_salary
FROM employees
GROUP BY department;Logic:
GROUP BY department groups rows by department.SUM(salary) calculates the total salary per department.
Produces one row per department with the summed salary.
This query was especially helpful when I needed to compare departments side by side. It made it obvious that one team had a much higher payroll than the others, which led us to catch a few outlier salaries that had been entered incorrectly.
Using AVG()
AVG() is one of those functions that looks straightforward, but I have learned not to trust it blindly until I check what is happening with the underlying rows. If a department has a mix of junior and senior staff, the average can look fine even when the spread is very wide.
The AVG() function calculates the average value of a numeric column.
Example: Average Salary
SELECT AVG(salary) AS avg_salary
FROM employees;Step-by-Step Analysis:
AVG(salary) adds all salaries and divides by the number of rows.
Provides insight into overall compensation levels.
Example: Average Salary by Department
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;Logic:
Group employees by department.
Calculate the average salary for employees within each group.
In our case, this query helps explain why a certain department has a small number of employees, yet its salaries appear “expensive” on paper. The department’s average salary is significantly higher than the company-wide average, which aligns with our actual recruitment practices.
Using MAX() and MIN()
When I want a quick range check, I usually combine MAX() and MIN() first before looking at anything more complicated. It is a fast way to catch bad data. A salary that is way too high or oddly low usually shows up here before it shows up anywhere else.
MAX() returns the highest value, while MIN() returns the lowest.
Example 1: Highest and Lowest Salaries
SELECT MAX(salary) AS highest_salary, MIN(salary) AS lowest_salary
FROM employees;Step-by-Step Analysis:
MAX(salary) identifies the highest salary.MIN(salary) identifies the lowest salary.
Useful for identifying top performers or benchmarking compensation.
This query saved me once when a test record had a salary entered as 999999.99 by mistake. The average barely moved, so the issue was easy to miss there, but MAX() made it obvious right away.
Example 2: Highest Salary by Department
SELECT department, MAX(salary) AS highest_salary
FROM employees
GROUP BY department;Logic:
GROUP BY department ensures calculations are per department.MAX(salary) returns the highest salary in each group.
I like this tool because it provides a quick overview of salary ranges for each team without requiring any data to be exported to Excel first.
Combining Multiple Aggregate Functions
I later realized that I had been repeatedly querying the same table with different queries. So I combined them into a single statement, which greatly simplified the debugging process. Instead of checking the count, total, average, maximum, and minimum values one by one, I could scan the entire result set and immediately spot the outliers.
SELECT department,
COUNT(*) AS employee_count,
SUM(salary) AS total_salary,
AVG(salary) AS avg_salary,
MAX(salary) AS highest_salary,
MIN(salary) AS lowest_salary
FROM employees
GROUP BY department;Analysis:
Returns one line per department.
Includes multiple metrics: number of employees, total payroll, average salary, highest and lowest salary.
Provides a comprehensive decision summary.
During the validation process, I used this query repeatedly. When data from a particular department showed anomalies, I could immediately determine whether the problem stemmed from outliers in employee numbers or salaries, or simply from incorrect data entry.
After processing these queries, I summarized a few points:
Use GROUP BY with caution: I’ve learned that when you need to calculate totals at the category level, never omit grouping. Otherwise, the results, while technically correct, can still mislead users.
Handle null values: Aggregate functions typically ignore NULL values. This is more important than it sounds, especially when the data is incomplete or still being imported.
Combine with ORDER BY: I often sort the summarized results to make it easier to find the maximum or minimum values.
Optimize queries: In large tables, creating indexes on columns used in WHERE or GROUP BY can significantly improve performance. I noticed this particularly noticeable when the employee table had hundreds of thousands of rows.
Clearly label results: Using AS as the output name can save time later, especially when the same query is reused in reports or dashboards.