SQL COUNT() Function

Technical writer

SQL

13.09.2024

Reading time: 10 min

SQL, also known as the Structured Query Language, is one of the most widely used tools for managing information in relational databases. It offers numerous functions and tools for analyzing and organizing data. One often overlooked but highly useful feature of SQL is the use of counters. The COUNT() function serves as this counter, allowing you to count the number of rows in a table or calculate the number of unique values in a specific column. Despite its apparent simplicity, COUNT() can sometimes pose challenges, especially when dealing with large datasets or unfamiliar data structures.

In this article, we'll explore how to use the COUNT() function in SQL to count rows. Through practical examples, we will see how this function can simplify database analysis. By mastering this material, you'll be able to use COUNT() effectively for data analysis and processing.

The COUNT() Function

A counter in an SQL table, or a "row counter," is a functionality that allows you to count the number of rows in a table that meet a specific condition. This can be useful in various situations, from determining the number of records in a table to more complex analytical queries that count unique records based on certain criteria.

One of the most common uses of the row counter in SQL is through the COUNT() function. This built-in function calculates the number of rows in a table or the number of unique values in a column. It can be applied to a single column or to the entire table.

The syntax for the COUNT() function in SQL is as follows:

SELECT COUNT(column_name)
FROM table_name
WHERE condition;

column_name: The name of the column whose rows you want to count.
table_name: The name of the table from which the data is taken.
condition: An optional condition to filter the rows before counting.

If your goal is to count all rows in a table, regardless of their specific values, you can use the * symbol instead of specifying a column name, as shown below:

SELECT COUNT(*)
FROM table_name;

Usage Examples

Let's look at several examples of how to use the COUNT() operator in SQL.

For this tutorial, we will use the SQLite database management system. You can download the DB Browser for SQLite, a free, open-source program for creating and editing SQLite databases.

Start the DB Browser for SQLite and go to the "SQL" tab, where we will write queries to create tables and count rows.

Counting Total Records in a Table

Suppose you have a table named Purchases that contains order information:

PurchaseID	BuyerID	Product
1	101	Apple
2	102	Banana
3	103	Cherry
4	101	Banana
5	104	Cherry

To create this table, use the following query:

CREATE TABLE Purchases (
    PurchaseID INTEGER PRIMARY KEY,
    BuyerID INTEGER,
    Product TEXT
);

After running the SQL query, go to the "DB Structure" tab to verify that the table was successfully created.

Now, let's populate the table with data by running the following query:

INSERT INTO Purchases (PurchaseID, BuyerID, Product) VALUES (1, 101, 'Apple');
INSERT INTO Purchases (PurchaseID, BuyerID, Product) VALUES (2, 102, 'Banana');
INSERT INTO Purchases (PurchaseID, BuyerID, Product) VALUES (3, 103, 'Cherry');
INSERT INTO Purchases (PurchaseID, BuyerID, Product) VALUES (4, 101, 'Banana');
INSERT INTO Purchases (PurchaseID, BuyerID, Product) VALUES (5, 104, 'Cherry');

Now, if you want to know the total number of orders, you can use a simple COUNT() with the * symbol:

SELECT COUNT(*)
FROM Purchases;

This will count and return the total number of records in the Purchases table. The result of this query will be 5.

Counting Unique Records in a Column

Suppose you have a Buyers table, and you want to know how many unique countries are represented among your customers' addresses:

BuyerID	FirstName	BuyerCountry
101	John	Canada
102	Alice	USA
103	Bob	USA
104	Diego	Mexico
105	Peter	Canada

Create the Buyers table as follows:

CREATE TABLE Buyers (
    BuyerID INTEGER PRIMARY KEY,
    FirstName TEXT,
    BuyerCountry TEXT
);

And fill it with data:

INSERT INTO Buyers (BuyerID, FirstName, BuyerCountry) VALUES (101, 'John', 'Canada');
INSERT INTO Buyers (BuyerID, FirstName, BuyerCountry) VALUES (102, 'Alice', 'USA');
INSERT INTO Buyers (BuyerID, FirstName, BuyerCountry) VALUES (103, 'Bob', 'USA');
INSERT INTO Buyers (BuyerID, FirstName, BuyerCountry) VALUES (104, 'Diego', 'Mexico');
INSERT INTO Buyers (BuyerID, FirstName, BuyerCountry) VALUES (105, 'Peter', 'Canada');

You can see that the BuyerCountry column includes several countries, but some are repeated (e.g., Canada). If you want to count the number of unique countries, you can use COUNT() with the DISTINCT keyword:

SELECT COUNT(DISTINCT BuyerCountry)
FROM Buyers;

This will count and return the number of unique countries in the BuyerCountry column of the Buyers table. The result of this query will be 2.

Counting with a Condition (Data Filtering)

Sometimes, you only want to count rows that meet a certain condition. Orders in this table are made by customers from different countries:

PurchaseID	BuyerID	Product	BuyerCountry
1	101	Apple	Canada
2	102	Banana	USA
3	103	Cherry	USA
4	101	Banana	Canada
5	104	Cherry	Canada

We already have this Purchases table, but it lacks the BuyerCountry column. Let’s add it:

ALTER TABLE Purchases
ADD BuyerCountry TEXT;

And populate it with data:

UPDATE Purchases
SET BuyerCountry = 'Canada'
WHERE BuyerID IN (101, 104);

UPDATE Purchases
SET BuyerCountry = 'USA'
WHERE BuyerID IN (102, 103);

Suppose you want to know how many orders were made by customers from Canada. You can use COUNT() with the WHERE condition:

SELECT COUNT(*)
FROM Purchases
WHERE BuyerCountry = 'Canada';

This SQL query will count and return only the orders made by customers from Canada. The result of this query will be 3.

Counting with Data Grouping (GROUP BY)

When you need to count records within a specific group, the GROUP BY function is used.

Example table Purchases:

PurchaseID	BuyerID	Product
1	101	Apple
2	102	Banana
3	103	Cherry
4	101	Banana
5	104	Cherry
6	101	Apple

Let’s modify the existing Purchases table by adding a new row:

INSERT INTO Purchases (PurchaseID, BuyerID, Product)
VALUES (6, 101, 'Apple');

For example, if you want to find out how many orders each customer has placed, you can run this query:

SELECT BuyerID, COUNT(*)
FROM Purchases
GROUP BY BuyerID;

This query groups the orders by BuyerID and counts the number of orders for each customer. The result will be:

BuyerID	*COUNT()**
101	3
102	1
103	1
104	1

Combining COUNT and AVG

Imagine you have a Ratings table with product ratings by users:

RatingID	ProductID	UserID	Rating
1	101	1	4.0
2	102	2	3.5
3	101	3	4.5
4	103	1	5.0
5	101	2	3.0
6	102	3	4.5

Create the table:

CREATE TABLE Ratings (
    RatingID INT PRIMARY KEY,
    ProductID INT,
    UserID INT,
    Rating DECIMAL(2,1)
);

INSERT INTO Ratings (RatingID, ProductID, UserID, Rating)
VALUES 
(1, 101, 1, 4.0),
(2, 102, 2, 3.5),
(3, 101, 3, 4.5),
(4, 103, 1, 5.0),
(5, 101, 2, 3.0),
(6, 102, 3, 4.5);

To find out how many ratings each product received and their average rating, you can run this query:

SELECT ProductID, COUNT(*), AVG(Rating)
FROM Ratings
GROUP BY ProductID;

The result will be:

ProductID	*COUNT()**	AVG(Rating)
101	3	3.83
102	2	4.0
103	1	5.0

Using COUNT with HAVING

If you want to filter the count results, use HAVING. Suppose you have a large Ratings table, and you want to find which products received more than 100 ratings:

SELECT ProductID, COUNT(*)
FROM Ratings
GROUP BY ProductID
HAVING COUNT(*) > 100;

In this case, the result will be 0, as no product has received more than 100 ratings in the current data. The HAVING clause is used after GROUP BY.

Using COUNT with JOIN

We have two tables: Orders and Customers.

Our Orders table:

OrderID	ProductID	CustomerID
1	201	1
2	202	1
3	203	2
4	204	3

You can create it like this:

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    ProductID INT,
    CustomerID INT
);

INSERT INTO Orders (OrderID, ProductID, CustomerID)
VALUES 
(1, 201, 1),
(2, 202, 1),
(3, 203, 2),
(4, 204, 3);

The Customers table:

CustomerID	FirstName	CustomerCountry
1	John	Canada
2	Jack	USA
3	Diego	Mexico

Create the Customers table:

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    FirstName VARCHAR(255),
    CustomerCountry VARCHAR(255)
);


INSERT INTO Customers (CustomerID, FirstName, CustomerCountry)
VALUES 
(1, 'John', 'Canada'),
(2, 'Jack', 'USA'),
(3, 'Diego', 'Mexico');

To join the two tables and count how many orders were placed by customers from Canada, run the following query:

SELECT COUNT(*)
FROM Orders
LEFT JOIN Customers ON Customers.CustomerID = Orders.CustomerID
WHERE Customers.CustomerCountry = 'Canada';

This query will return the total number of orders made by customers from Canada.

Sorting Data with ORDER BY

Suppose you have an Employees table with employee details:

EmployeeID	FirstName	LastName	Salary
1	Diego	Alonso	55000
2	Peter	Smith	75000
3	Alexa	Brown	70000
4	James	Smith	80000
5	Maria	Alonso	90000

Create the Employees table:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(255),
    LastName VARCHAR(255),
    Salary INT
);

INSERT INTO Employees (EmployeeID, FirstName, LastName, Salary)
VALUES 
(1, 'Diego', 'Alonso', 55000),
(2, 'Peter', 'Smith', 75000),
(3, 'Alexa', 'Brown', 70000),
(4, 'James', 'Smith', 80000),
(5, 'Maria', 'Alonso', 90000);

You want to know how many employees earn more than 60,000, grouped by salary, and sorted in descending order. Run the query:

SELECT Salary, COUNT(*)
FROM Employees
WHERE Salary > 60000
GROUP BY Salary
ORDER BY Salary DESC;

The result will be:

Salary	*COUNT()**
90000	1
80000	1
75000	1
70000	1

Counting with Multiple Conditions (AND/OR)

Suppose you have a Sales table with product sales data:

SaleID	Product	Price	Quantity
1	Apple	20	5
2	Banana	15	10
3	Cherry	10	15
4	Apple	20	20
5	Banana	15	25

Create the Sales table:

CREATE TABLE Sales (
    SaleID INT PRIMARY KEY,
    Product VARCHAR(255),
    Price INT,
    Quantity INT
);


INSERT INTO Sales (SaleID, Product, Price, Quantity)
VALUES 
(1, 'Apple', 20, 5),
(2, 'Banana', 15, 10),
(3, 'Cherry', 10, 15),
(4, 'Apple', 20, 20),
(5, 'Banana', 15, 25);

To find how many times the product Apple was sold in quantities less than 10 or Banana in quantities greater than 20, use this query:

SELECT COUNT(*)
FROM Sales
WHERE (Product = 'Apple' AND Quantity < 10) OR (Product = 'Banana' AND Quantity > 20);

This query will return 2 based on the specified conditions.

Key Points When Using COUNT()

Handling NULL Values: COUNT(column_name) excludes rows with NULL values. To include them, use COUNT(*).
Using DISTINCT: COUNT() with DISTINCT counts only unique values in a column.
GROUP BY: Often used with COUNT() to count the number of rows in each group.
Integer Results: COUNT() always returns an integer result, regardless of the argument’s data type.
Subqueries: COUNT() can be used in subqueries to count rows meeting specific criteria.
Triggers: COUNT() can be used in SQL triggers to count modified rows.
Large Data: For large datasets, COUNT() queries can take time, so optimization may be necessary.
JOIN Queries: Complex relationships in JOIN queries require a solid understanding of the database structure for accurate counting.

Conclusion

We've covered the main features of the COUNT() function and applied it to practical SQL examples. With this knowledge, you can effectively process and analyze data, regardless of its size or complexity.

Hostman provides a SQL database hosting for your needs.

SQL