COUNT function behavior in SQL

Introduction

The COUNT function helps you find out how many rows or values are in a table or result. It is useful to quickly count items without looking at each one.

You want to know how many customers are in your database.

You need to count how many orders were placed in a month.

You want to find out how many products have a price listed.

You want to count how many employees work in each department.

Syntax

SQL

COUNT(column_name)
COUNT(*)
COUNT(DISTINCT column_name)

COUNT(column_name) counts only rows where the column is NOT NULL.

COUNT(*) counts all rows, including those with NULLs in any column.

COUNT(DISTINCT column_name) counts unique non-NULL values in the column.

Examples

Counts all rows in the employees table.

SQL

SELECT COUNT(*) FROM employees;

Counts how many customers have an email (ignores NULL emails).

SQL

SELECT COUNT(email) FROM customers;

Counts how many different departments exist.

SQL

SELECT COUNT(DISTINCT department) FROM employees;

Sample Program

This example creates a sales table with some rows. It counts total rows, counts rows where product is not NULL, and counts unique products.

SQL

CREATE TABLE sales (
  id INT,
  product VARCHAR(20),
  quantity INT
);

INSERT INTO sales VALUES
(1, 'Apple', 10),
(2, 'Banana', NULL),
(3, 'Apple', 5),
(4, NULL, 7);

SELECT COUNT(*) AS total_rows FROM sales;
SELECT COUNT(product) AS product_count FROM sales;
SELECT COUNT(DISTINCT product) AS unique_products FROM sales;

Important Notes

COUNT(*) is the safest way to count all rows regardless of NULLs.

COUNT(column_name) ignores NULL values in that column.

Use COUNT(DISTINCT column_name) to count unique values only.

Summary

COUNT helps count rows or values in a table.

COUNT(*) counts all rows, including NULLs.

COUNT(column) counts only non-NULL values in that column.

COUNT(DISTINCT column) counts unique non-NULL values.