SQL COUNT, AVG, and SUM Functions – Examples, NULLs, and GROUP BY | DbSchema

Table of Contents

1. What COUNT, AVG, and SUM do
2. Sample table for the examples
3. SQL COUNT()
4. SQL AVG()
5. SQL SUM()
6. DISTINCT and conditional aggregation
7. Use aggregate functions together
8. GROUP BY and HAVING with aggregate functions
9. Aggregate functions vs window functions
10. How NULL values affect results
11. Common mistakes and performance tips
12. Use aggregate queries in DbSchema
13. FAQ
14. Conclusion

The SQL COUNT(), AVG(), and SUM() functions are aggregate functions. They summarize many rows into one result, which makes them essential for reporting, dashboards, analytics, billing, and quality checks.

This guide focuses on the differences between these functions, how they behave with GROUP BY, HAVING, DISTINCT, and NULL, and how to test aggregate queries safely in DbSchema before you use them in production code.

What COUNT, AVG, and SUM do

The three functions are often used together:

SELECT COUNT(*) AS total_orders,
       AVG(order_total) AS average_order_value,
       SUM(order_total) AS total_revenue
FROM orders;

That combined style is one of the biggest content gaps in weaker tutorials, which often explain each aggregate in isolation but do not show how analysts actually use them together.

Sample table for the examples

CREATE TABLE Employees (
    employee_id INT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    salary INT,
    department VARCHAR(50)
);

INSERT INTO Employees (employee_id, first_name, last_name, salary, department)
VALUES
    (1, 'John', 'Doe', 50000, 'Sales'),
    (2, 'Jane', 'Doe', 60000, 'IT'),
    (3, 'Jim', 'Beam', 55000, 'Sales'),
    (4, 'Jack', 'Daniels', NULL, 'IT'),
    (5, 'Johnny', 'Walker', 65000, 'Finance');

We will reuse this table throughout the article so the examples stay easy to compare.

SQL COUNT()

COUNT() counts rows or non-NULL values.

COUNT(*) vs COUNT(column) vs COUNT(DISTINCT column)

SELECT COUNT(*) AS total_rows,
       COUNT(salary) AS rows_with_salary,
       COUNT(DISTINCT department) AS unique_departments
FROM Employees;

Result

Why the results differ

• COUNT(*) counts every row
• COUNT(salary) ignores NULL values in salary
• COUNT(DISTINCT department) counts unique department values only

That distinction is one of the most common interview and debugging questions about SQL aggregates.

SQL AVG()

AVG() returns the arithmetic mean of a numeric column.

SELECT AVG(salary) AS average_salary
FROM Employees;

Result

AVG() ignores NULL values automatically, which is why the row with no salary does not drag the result down.

AVG with DISTINCT

SELECT AVG(DISTINCT salary) AS average_distinct_salary
FROM Employees;

Use DISTINCT only when duplicate numeric values would distort the meaning of the result. It is valid SQL, but it is not always the correct business answer.

SQL SUM()

SUM() adds together numeric values.

SELECT SUM(salary) AS total_salary
FROM Employees;

Result

Like AVG(), SUM() ignores NULL values by default.

SUM with filtering

SELECT SUM(salary) AS total_high_salary
FROM Employees
WHERE salary > 50000;

This pattern is common in finance and reporting queries because it combines a row-level filter with an aggregate result.

DISTINCT and conditional aggregation

Two of the most common follow-up questions are "When should I use DISTINCT?" and "How do I calculate totals by condition in one query?"

DISTINCT with aggregate functions

SELECT COUNT(DISTINCT department) AS unique_departments,
       AVG(DISTINCT salary) AS average_distinct_salary
FROM Employees;

Use DISTINCT only when duplicate values would distort the business meaning. It is valid SQL, but it is not always the right metric.

Conditional aggregation with CASE

SELECT
    COUNT(*) AS employee_count,
    SUM(CASE WHEN department = 'Sales' THEN salary ELSE 0 END) AS sales_payroll,
    AVG(CASE WHEN department = 'IT' THEN salary END) AS it_average_salary
FROM Employees;

Conditional aggregation is a practical reporting pattern because it calculates several KPIs in one scan of the table.

Use aggregate functions together

In practice, analysts rarely run COUNT(), AVG(), and SUM() one at a time. They usually collect several measures in one query:

SELECT COUNT(salary) AS salaries_recorded,
       MIN(salary) AS minimum_salary,
       MAX(salary) AS maximum_salary,
       AVG(salary) AS average_salary,
       SUM(salary) AS total_salary
FROM Employees;

This gives a compact profile of the dataset in one pass.

Even though this page focuses on COUNT(), AVG(), and SUM(), it is useful to remember that MIN() and MAX() often belong in the same family of reporting queries.

GROUP BY and HAVING with aggregate functions

Group by department

SELECT department,
       COUNT(*) AS employee_count,
       AVG(salary) AS average_salary,
       SUM(salary) AS total_salary
FROM Employees
GROUP BY department
ORDER BY total_salary DESC;

Result

Filter groups with HAVING

SELECT department,
       COUNT(*) AS employee_count,
       AVG(salary) AS average_salary,
       SUM(salary) AS total_salary
FROM Employees
GROUP BY department
HAVING SUM(salary) > 60000
ORDER BY total_salary DESC;

Use HAVING when the filter depends on an aggregate result. If you want a deeper walkthrough, read SQL HAVING Clause.

Aggregate functions vs window functions

Regular aggregate functions collapse many rows into fewer rows. Window functions keep the detail rows and add calculations beside them.

SELECT employee_id,
       department,
       salary,
       AVG(salary) OVER (PARTITION BY department) AS department_average
FROM Employees;

Use a grouped aggregate when you want one row per group. Use a window function when you want every row plus a summary value for comparison. For a broader analytics example, see Window Functions in SQL.

How NULL values affect results

NULL handling is one of the biggest sources of confusion with aggregate functions.

If you intentionally want to treat NULL as zero, use COALESCE():

SELECT AVG(COALESCE(salary, 0)) AS average_salary_including_missing_values
FROM Employees;

Be careful: replacing NULL with 0 changes the business meaning of the result. Sometimes that is correct, sometimes it is not.

Common mistakes and performance tips

Common mistakes

1. Confusing COUNT(*) with COUNT(column)
2. Using DISTINCT without understanding the business meaning
3. Forgetting that AVG() and SUM() ignore NULL
4. Returning grouped and non-grouped columns together incorrectly
5. Using HAVING when WHERE would filter rows earlier and faster

Practical performance tip

If a condition does not depend on an aggregate result, put it in WHERE, not HAVING:

SELECT department, SUM(salary) AS total_salary
FROM Employees
WHERE salary > 0
GROUP BY department;

That reduces the number of rows that need to be grouped.

If you need a refresher on row filters and sort order, see SQL WHERE Clause, SQL ORDER BY, and the broader SQL Aggregate Functions.

Use aggregate queries in DbSchema

DbSchema is helpful when you are working with aggregate queries because it lets you move between the schema, the data, and the SQL itself.

You can:

1. connect to a real database using the PostgreSQL JDBC driver or MySQL JDBC driver
2. build and test aggregate queries in the SQL Editor
3. preview table contents before writing grouped filters
4. switch to the Query Builder if you want to inspect joins visually before adding aggregates
5. document the grouped metrics for teammates in the schema documentation

That workflow is especially useful when you are validating reports or explaining grouped metrics to other team members.

FAQ

Does COUNT(*) include NULL values?

COUNT(*) counts rows, so yes. COUNT(column) counts only non-NULL values in that column.

Does AVG() ignore NULL?

Yes. AVG() ignores NULL values unless you replace them explicitly with COALESCE().

Does SUM() ignore NULL?

Yes. SUM() ignores NULL values for the aggregated column.

When should I use COUNT(DISTINCT column)?

Use it when you need the number of unique values instead of the number of rows.

Can I use COUNT(), AVG(), and SUM() in the same query?

Yes, and that is often the most useful pattern in reporting queries.

Is COUNT(1) faster than COUNT(*)?

In modern relational databases, usually no. Query optimizers treat them the same in most cases, so choose the form that is clearest for your team.

Conclusion

COUNT(), AVG(), and SUM() are the core aggregate functions every SQL user needs. Once you understand how they differ with NULL, DISTINCT, GROUP BY, and HAVING, you can write much stronger reporting and analytics queries.

Use DbSchema to test the aggregates against live data, inspect the underlying tables, and refine grouped queries before they reach production dashboards or application code.