COUNT function in MySQL

Introduction

The COUNT function helps you find out how many rows or items are in a group or table. It is useful when you want to count things like how many people are in a list or how many orders were made.

Counting how many customers bought a product.

Finding the number of employees in a department.

Checking how many orders were placed on a certain day.

Counting the total number of books in a library database.

Syntax

MySQL

SELECT COUNT(column_name) FROM table_name;

-- Or to count all rows:
SELECT COUNT(*) FROM table_name;

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

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

Examples

Counts all rows in the employees table.

MySQL

SELECT COUNT(*) FROM employees;

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

MySQL

SELECT COUNT(email) FROM customers;

Counts employees in each department.

MySQL

SELECT department, COUNT(*) FROM employees GROUP BY department;

Sample Program

This example creates a table 'orders' and inserts 4 rows. Then it counts all orders and counts only orders that have a customer_id.

MySQL

CREATE TABLE orders (
  order_id INT,
  customer_id INT,
  order_date DATE
);

INSERT INTO orders VALUES
(1, 101, '2024-01-01'),
(2, 102, '2024-01-02'),
(3, NULL, '2024-01-03'),
(4, 104, NULL);

SELECT COUNT(*) AS total_orders FROM orders;
SELECT COUNT(customer_id) AS orders_with_customer FROM orders;

Important Notes

COUNT(column) ignores NULL values in that column.

COUNT(*) counts every row, even if some columns have NULL.

Use GROUP BY with COUNT to count items in groups.

Summary

COUNT helps you find how many rows or items exist.

COUNT(*) counts all rows, COUNT(column) counts non-NULL values in that column.

It is useful for summaries and reports.