SQL COUNT() Function
COUNT() returns the number of rows that match a condition:
+----+-----------+------------+--------+-----------+
| ID | Name | Department | Salary | City |
+----+-----------+------------+--------+-----------+
| 1 | Priya K. | Sales | 55000 | Mumbai |
| 2 | John D. | Marketing | 62000 | London |
| 3 | Arun M. | IT | 78000 | Delhi |
| 4 | Emma W. | Sales | 51000 | Sydney |
| 5 | Ravi S. | IT | 72000 | Mumbai |
| 6 | Sara L. | HR | 58000 | New York |
+----+-----------+------------+--------+-----------+Count all rows:
SELECT COUNT(*) AS TotalEmployees
FROM Employees;+----------------+ | TotalEmployees | +----------------+ | 6 | +----------------+
Count employees in IT:
SELECT COUNT(*) AS ITCount
FROM Employees
WHERE Department = 'IT';+---------+ | ITCount | +---------+ | 2 | +---------+
💡 COUNT(*) vs COUNT(column)
COUNT(*) counts all rows including those with NULL values. COUNT(column) counts only rows where that column is NOT NULL. Use COUNT(*) for total row counts and COUNT(column) when you need to exclude NULLs.
SQL SUM() Function
SUM() adds up all values in a numeric column:
SELECT SUM(Salary) AS TotalPayroll
FROM Employees;+--------------+ | TotalPayroll | +--------------+ | 376000 | +--------------+
Total salaries for Sales department: Use the WHERE clause to filter before summing:
SELECT SUM(Salary) AS SalesPayroll
FROM Employees
WHERE Department = 'Sales';+--------------+ | SalesPayroll | +--------------+ | 106000 | +--------------+
SQL AVG() Function
AVG() calculates the average (mean) of all values in a numeric column:
SELECT AVG(Salary) AS AvgSalary
FROM Employees;+------------+ | AvgSalary | +------------+ | 62666.6667 | +------------+
Combining All Three
You can use all aggregate functions together with MIN and MAX (covered in the previous chapter) for a complete data summary:
SELECT
COUNT(*) AS TotalEmployees,
SUM(Salary) AS TotalPayroll,
AVG(Salary) AS AvgSalary,
MIN(Salary) AS MinSalary,
MAX(Salary) AS MaxSalary
FROM Employees;+--------+---------+------------+-----------+-----------+ | Total | Payroll | AvgSalary | MinSalary | MaxSalary | +--------+---------+------------+-----------+-----------+ | 6 | 376000 | 62666.6667 | 51000 | 78000 | +--------+---------+------------+-----------+-----------+
Aggregate Functions Summary
| Function | What It Does | Ignores NULL? |
|---|---|---|
COUNT(*) |
Counts all rows | No |
COUNT(col) |
Counts non-NULL values in column | Yes |
SUM(col) |
Adds up all values | Yes |
AVG(col) |
Calculates average of all values | Yes |
MIN(col) |
Finds smallest value | Yes |
MAX(col) |
Finds largest value | Yes |
For a complete reference of all aggregate functions, see the official MySQL aggregate functions documentation. These functions become even more powerful when combined with GROUP BY, which you will learn in Chapter 25.
If you work with data regularly, explore our ready-made Dashboard Templates on NextGenTemplates that use SQL-powered data for business reporting.
📺 Visit our YouTube channel @NeoTechNavigators for step-by-step video tutorials and dashboard demos on this topic.
🧪 Try It Yourself
Write a query that shows the total number of employees in Mumbai, their total salary, and their average salary.
SELECT
COUNT(*) AS MumbaiCount,
SUM(Salary) AS MumbaiTotal,
AVG(Salary) AS MumbaiAvg
FROM Employees
WHERE City = 'Mumbai';Expected Output:
+-------------+-------------+-----------+ | MumbaiCount | MumbaiTotal | MumbaiAvg | +-------------+-------------+-----------+ | 2 | 127000 | 63500 | +-------------+-------------+-----------+
📝 What You Learned in This Chapter
COUNT()counts rows — useCOUNT(*)for all rows,COUNT(col)for non-NULL valuesSUM()adds up all numeric values in a columnAVG()calculates the average of numeric values- All aggregate functions can be combined with WHERE to filter first
- SUM, AVG, MIN, and MAX all ignore NULL values automatically
- Always use aliases with AS for readable output column names
Frequently Asked Questions
COUNT(*) counts all rows regardless of NULL values. COUNT(column) counts only rows where that specific column has a value (is not NULL). Use COUNT(*) for total row counts and COUNT(column) when NULLs should be excluded.SELECT Department, AVG(Salary) FROM Employees GROUP BY Department shows the average salary per department. GROUP BY is covered in detail in Chapter 25.📖 Chapter 14 of 30 — SQL Tutorial on Neotech Navigators
