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

COUNT counts rows, SUM adds up the values in a column, and AVG returns the average of those values. They are aggregate functions: each one turns many rows into one number. For example, one query can return the number of employees, their total salary and their average salary.

A query with all three functions has this shape:

SELECT COUNT(*), SUM(column), AVG(column)
FROM table
WHERE condition
GROUP BY column;

• COUNT(*) counts the rows. COUNT(column) skips the rows where the column holds NULL, the mark of a missing value.
• SUM(column) adds up the values of a numeric column, and AVG(column) returns their average.
• WHERE condition is optional. It chooses which rows the functions read.
• GROUP BY column is optional. Without GROUP BY, the query returns one row for the whole table. With GROUP BY, the query returns one row for each value of that column.

Create a sample table of five employees, and fill it:

CREATE TABLE employees (
  name   varchar(20),
  dept   varchar(10),
  salary int
);

INSERT INTO employees VALUES
  ('Anna', 'IT',    4000),
  ('Ben',  'IT',    6000),
  ('Carl', 'HR',    5000),
  ('Dora', 'HR',    4000),
  ('Eva',  'Sales', NULL);

Eva's salary is missing, so it is NULL. Every result below comes from three database engines: PostgreSQL 18, MySQL 9.1 and SQLite 3.50. All three returned the same values, except where an engine is named.

COUNT(*) and COUNT(column)

COUNT(*) counts every row. COUNT(column) counts only the rows where that column is not NULL. For example, you can count all employees, and the employees who have a salary.

SELECT COUNT(*)      AS all_rows,
       COUNT(salary) AS with_salary
FROM employees;

The table has five rows, so COUNT(*) returns 5. Eva's salary is NULL, so COUNT(salary) leaves her out and returns 4. Use COUNT(*) for the number of rows, and COUNT(column) for the number of values that are filled in.

COUNT(DISTINCT column)

COUNT(DISTINCT column) counts how many different values a column holds. For example, you can count the departments that the employees work in.

SELECT COUNT(dept)          AS dept_rows,
       COUNT(DISTINCT dept) AS departments
FROM employees;

All five rows have a department, so COUNT(dept) returns 5. IT and HR appear twice each, but DISTINCT counts each department once. COUNT(DISTINCT dept) returns 3: IT, HR and Sales.

SUM

SUM adds up the values of a numeric column. For example, you can add up all salaries to get the payroll.

SELECT SUM(salary) AS total_salary
FROM employees;

SUM adds Anna's 4000, Ben's 6000, Carl's 5000 and Dora's 4000. SUM skips Eva's NULL.

AVG

AVG returns the average of a numeric column: the sum of the values, divided by the number of values that are not NULL. For example, you can get the average salary of the employees.

SELECT AVG(salary) AS avg_salary
FROM employees;

AVG divides the total of 19000 by 4, because only four employees have a salary.

All three engines return the value 4750, but print it with different decimal places. PostgreSQL prints the 16 zeros of the table above, MySQL 9.1 prints 4750.0000, and SQLite prints 4750.0.

Practice this in Lesson 6 of the SQL tutorial: you run COUNT, SUM, AVG, MIN and MAX on a shop database in your browser, and each exercise is checked.

GROUP BY

GROUP BY splits the rows into groups, one group for each value of a column. COUNT, SUM and AVG then return one value for each group. For example, you can get the number of employees, the payroll and the average salary of each department.

SELECT dept,
       COUNT(*)    AS employees,
       SUM(salary) AS total_salary,
       AVG(salary) AS avg_salary
FROM employees
GROUP BY dept
ORDER BY dept;

HR holds Carl and Dora, so it has 2 employees, a total of 9000 and an average of 4500. IT holds Anna and Ben. Sales holds only Eva, and her salary is NULL: COUNT(*) returns 1, while SUM and AVG return NULL.

To show each department's total beside every employee's row, use SUM with OVER. You learn OVER in Window functions in SQL.

HAVING

HAVING keeps only the groups that match a condition. HAVING runs after COUNT, SUM and AVG, so its condition can use their results. For example, you can keep the departments whose payroll is at least 9000.

SELECT dept, SUM(salary) AS total_salary
FROM employees
GROUP BY dept
HAVING SUM(salary) >= 9000
ORDER BY dept;

HR's total of 9000 and IT's 10000 meet the condition, so both stay. Sales has a total of NULL. A comparison with NULL is never true, so HAVING drops Sales.

Put a condition on a column, such as dept = 'IT', in WHERE. WHERE removes rows before GROUP BY makes the groups. Put a condition on COUNT, SUM or AVG in HAVING, because WHERE can't use these functions.

What goes wrong

AVG skips NULL instead of counting it as 0

AVG returned 4750, the average of the four salaries that are filled in. If a missing salary should count as 0, use COALESCE. COALESCE(salary, 0) returns 0 where salary is NULL, and returns the salary everywhere else:

SELECT AVG(COALESCE(salary, 0)) AS avg_salary
FROM employees;

Now Eva counts as 0, and AVG divides 19000 by 5. Choose the average that your report needs: 4750 for the known salaries, or 3800 if a missing salary counts as 0.

SUM and AVG of no rows return NULL

No employee works in Finance, so the query below finds no rows:

SELECT COUNT(*)    AS all_rows,
       SUM(salary) AS total_salary,
       AVG(salary) AS avg_salary
FROM employees
WHERE dept = 'Finance';

COUNT returns 0, but SUM and AVG return NULL. PostgreSQL's documentation says that every aggregate function except count returns NULL when no rows are selected. If your code needs a number, write COALESCE(SUM(salary), 0), which returns 0 here.

DISTINCT inside SUM drops a real salary

Inside SUM, DISTINCT drops repeated values before adding. Anna and Dora both earn 4000, so SUM(DISTINCT salary) adds 4000 only once:

SELECT SUM(salary)          AS total_salary,
       SUM(DISTINCT salary) AS distinct_total
FROM employees;

The payroll is 19000, but the distinct total is 15000: one salary of 4000 is missing. Use DISTINCT inside COUNT, and keep DISTINCT out of a SUM or AVG of amounts.

A column next to COUNT without GROUP BY

Without GROUP BY, COUNT(*) returns one row for the whole table. That row has room for one name, but the name column holds five:

SELECT name, COUNT(*)
FROM employees;

PostgreSQL 18 rejects the query with column "employees.name" must appear in the GROUP BY clause or be used in an aggregate function. MySQL 9.1 rejects it too, because its ONLY_FULL_GROUP_BY mode is on by default.

SQLite 3.50 runs the query and returns one row: Anna and 5. SQLite took the name from one of the five rows.

AVG of an integer column in SQL Server

On SQL Server, AVG of an int column returns an int, so the average loses its decimal part. First convert the column to a decimal type with CAST: AVG(CAST(salary AS decimal(10, 2))). PostgreSQL, MySQL and SQLite return a decimal or floating-point average, so the decimal part stays.

COUNT(1) is not faster than COUNT(*)

COUNT(1) also counts the rows, and returns 5 here on all three engines. MySQL's manual says that InnoDB, MySQL's default storage engine, handles COUNT(*) and COUNT(1) the same way, with no difference in speed. Write COUNT(*), because it plainly counts rows.

Run the queries in DbSchema

The SQL Editor in DbSchema runs a query and shows the result in a grid under the editor. Press Editor in the toolbar, choose SQL Editor, paste a query from this article, and press Run.

The Query Builder in DbSchema writes a grouped query for you. DbSchema's menus call it the Query Editor.

1. Right-click a table on the diagram and choose Query Editor.
2. Right-click a column, choose Aggregate, then a function such as Count(*), Sum(?) or Avg(?).
3. Press Run.

DbSchema adds a GROUP BY over the other ticked columns, and the result grid under the builder lists one row per group.

Download DbSchema at https://dbschema.com/download.html and connect it to PostgreSQL, MySQL or SQLite. Paste the CREATE TABLE and INSERT statements into the SQL Editor, then run and change each query. Connecting to a database and the SQL Editor are part of the free Community Edition, and the Query Builder is part of DbSchema Pro.