sql_func_count

SQL COUNT() Function

The COUNT() function is an aggregate function that returns the number of rows matching a specified criterion. It’s essential for summarizing data, like finding out how many customers you have or how many orders were placed on a certain day.

SQL COUNT(column_name) Syntax

The COUNT(column_name) function returns the number of non-NULL values in the specified column.

SELECT COUNT(column_name)
FROM table_name
[WHERE condition];

SQL COUNT(*) Syntax

The COUNT(*) function returns the total number of rows in a table that satisfy the WHERE clause (if present), regardless of NULL values in any particular column. It counts each row.

SELECT COUNT(*)
FROM table_name
[WHERE condition];

SQL COUNT(DISTINCT column_name) Syntax

The COUNT(DISTINCT column_name) function returns the number of unique, non-NULL values in the specified column.

SELECT COUNT(DISTINCT column_name)
FROM table_name
[WHERE condition];

Note: COUNT(DISTINCT column_name) is standard SQL and supported by most modern database systems like PostgreSQL, MySQL, SQL Server, and Oracle.

Demo Database Schema

For the examples in this tutorial, let’s assume we have an Orders table with the following relevant columns:

Orders Table:
- OrderID (INT, Primary Key)
- CustomerID (INT)
- OrderDate (DATE)
- ShipperID (INT, might be NULL if not yet shipped)

And a Products table:

Products Table:
- ProductID (INT, Primary Key)
- ProductName (VARCHAR)
- SupplierID (INT)
- Category (VARCHAR)

SQL COUNT(column_name) Example

The following SQL statement counts the number of orders that have an assigned ShipperID (i.e., ShipperID is not NULL).

SELECT COUNT(ShipperID) AS NumberOfShippedOrders
FROM Orders;

This query counts only the rows where ShipperID has a value. If an order hasn’t been shipped and ShipperID is NULL, that row won’t be included in this specific count.

SQL COUNT(*) Example

The following SQL statement counts the total number of orders in the Orders table:

SELECT COUNT(*) AS TotalNumberOfOrders
FROM Orders;

If you want to count orders for a specific customer, say CustomerID 7:

SELECT COUNT(*) AS OrdersFromCustomer7
FROM Orders
WHERE CustomerID = 7;

SQL COUNT(DISTINCT column_name) Example

The following SQL statement counts the number of unique customers who have placed orders:

SELECT COUNT(DISTINCT CustomerID) AS NumberOfUniqueCustomers
FROM Orders;

Practical Application: COUNT() is used extensively for reporting and analytics. For example:

• Counting active users: SELECT COUNT(UserID) FROM Users WHERE IsActive = TRUE;
• Finding the number of products in each category: SELECT Category, COUNT(*) FROM Products GROUP BY Category;
• Checking how many unique visitors accessed a webpage on a given day.

Learning Obstacle Point: A common confusion is the difference between COUNT(*) and COUNT(column_name). Remember COUNT(*) counts all rows matching the WHERE clause, while COUNT(column_name) counts non-NULL values in that specific column.