SQL COUNT() Function

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:

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:

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:

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:

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:

Combining COUNT and AVG

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

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:

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:

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:

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:

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:

Counting with Multiple Conditions (AND/OR)

Suppose you have a Sales table with product sales data:

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.