SQL COUNT, AVG, and SUM with GROUP BY: Syntax and Examples | DbSchema

Table of contents

• What COUNT, AVG, and SUM do
• Sample table for the examples
• SQL COUNT()
• SQL AVG()
• SQL SUM()
• DISTINCT and conditional aggregation
• Use aggregate functions together
• GROUP BY and HAVING with aggregate functions
• Aggregate functions vs window functions
• How NULL values affect COUNT, AVG, and SUM
• Common mistakes and performance tips
• Use aggregate queries in DbSchema
• FAQ
• 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.

Need to validate aggregate queries on live data? Download DbSchema and test COUNT, AVG, and SUM with visual schema context.

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;

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');

Every example below uses this table.

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download

SQL COUNT()

COUNT() counts rows or non-NULL values[1].

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;

Why the results differ

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

SQL AVG()

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

SELECT AVG(salary) AS average_salary
FROM Employees;

AVG() ignores NULL and divides by the count of non-NULL values[2], so the row with no salary is left out of the divisor as well as the sum: 230000 / 4, not 230000 / 5.

AVG with DISTINCT

SELECT AVG(DISTINCT salary) AS average_distinct_salary
FROM Employees;

Use DISTINCT only when duplicate salaries would distort the average.

SQL SUM()

SUM() adds together numeric values.

SELECT SUM(salary) AS total_salary
FROM Employees;

Like AVG(), SUM() ignores NULL values[3], and it returns NULL rather than 0[4] when no rows match at all.

SUM with filtering

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

WHERE filters the rows before SUM() adds them up.

DISTINCT and conditional aggregation

DISTINCT with aggregate functions

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

COUNT(DISTINCT department) counts unique non-NULL departments[1]. Use DISTINCT only when duplicate values would distort the business meaning.

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 calculates several KPIs in one pass over the table.

Use aggregate functions together

Analysts rarely run these 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;

MIN() and MAX() belong in the same family and ignore NULL the same way[5].

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;

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 COUNT, AVG, and SUM

COUNT(*) counts every row, including rows that are entirely NULL[1], while COUNT(column) counts only the non-NULL values in that column[4]. SUM() and AVG() both ignore NULL[3], and AVG() divides by the count of non-NULL values[2], so a missing value shrinks the divisor instead of counting as zero. Against the five sample rows, one of which has a NULL salary:

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

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

Replacing NULL with 0 changes the business meaning: the divisor becomes 5 and the average drops to 46000.

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;

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 lets you move between the schema, the data, and the SQL itself in one window.

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, which the free Community Edition includes
3. browse table contents in the relational data editor before writing grouped filters (Pro)
4. switch to the Query Builder to inspect joins visually before adding aggregates (Pro)
5. document the grouped metrics for teammates in the schema documentation (Pro)

FAQ

Does COUNT(*) include NULL values?

Yes. COUNT(*) counts rows, so a row is counted even when every column in it is NULL[1]. COUNT(column) is the one that skips them, counting only non-NULL values.

Does AVG() ignore NULL?

Yes. AVG() skips NULL and divides by the count of non-NULL values[2], so five rows carrying one NULL salary are averaged over four. Use COALESCE() to count NULL as zero instead.

Does SUM() ignore NULL?

Yes. SUM() adds only the non-NULL values, and returns NULL rather than 0[5] when no rows match at all.

When should I use COUNT(DISTINCT column)?

Use it when you need unique values rather than rows. It counts distinct non-NULL values[1], so rows where the column is NULL are not counted at all.

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

Yes. One SELECT can carry all three: SELECT COUNT(*), AVG(salary), SUM(salary) FROM Employees.

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() differ most in how they treat NULL: COUNT(*) counts every row, COUNT(column) and SUM() and AVG() skip NULL, and AVG() divides by the non-NULL count. Get that right and GROUP BY, HAVING and DISTINCT follow.

Download DbSchema and run these aggregates against your own database. The free Community Edition covers the connection, the interactive diagram and the SQL Editor; Pro adds the visual query builder, relational data browse and the schema documentation.

Sources

1. COUNT (Transact-SQL) - Microsoft Learn
2. AVG (Transact-SQL) - Microsoft Learn
3. SUM (Transact-SQL) - Microsoft Learn
4. PostgreSQL Documentation: Aggregate Functions
5. MySQL Reference Manual: Aggregate Function Descriptions