SQL COUNT()

-- returns the number of values in the Orders table
SELECT COUNT(*)
FROM Orders;

Count() Syntax

The syntax of the SQL COUNT() function is:

SELECT COUNT(expression)
FROM table_name;

Here,

• expression can be *, column name or expression such as DISTINCT column_name
• table_name is the name of the table

For example,

-- returns the number of rows in the Customers table
SELECT COUNT(*)
FROM Customers;

Here, the above SQL command counts and returns the number of rows in the Customers table.

Aliases With COUNT()

In the above example, the field name in the result set is COUNT(*).

It is also possible to give custom names to these fields using the AS keyword. For example,

-- return the count of rows from customers table as total_customers
SELECT COUNT(*) AS total_customers
FROM Customers;

Here, the field name COUNT(*) is replaced by total_customers in the result set.

COUNT() With WHERE

We can use COUNT() with WHERE to count rows that have certain column values:

-- count of customers who live in the UK
SELECT COUNT(country) AS customers_in_UK
FROM Customers
WHERE country = 'UK';

Here, the SQL command returns the count of customers whose country is UK.

COUNT() With DISTINCT

If we need to count the number of unique rows, we can use the COUNT() function with the DISTINCT clause. For example,

-- count the unique countries in Customers table
SELECT COUNT(DISTINCT country)
FROM Customers;

Here, the SQL command returns the count of unique countries.

COUNT() With GROUP BY

The COUNT() function can be used with the GROUP BY clause to count the rows with similar values. For example,

-- count the number of customers in each country
SELECT country, COUNT(*) AS customers
FROM Customers
GROUP BY country;

Here, the SQL command returns the number of customers in each country.

COUNT() With HAVING Clause

We can use COUNT() with the HAVING clause as follows:

--count the number of rows by country and return the results for count greater than one
SELECT COUNT(customer_id), country
FROM Customers
GROUP BY country
HAVING COUNT(customer_id) > 1;

Here, the SQL command:

1. counts the number of rows by grouping them by country
2. returns the result set if their count is greater than 1.

COUNT() With NULL Values

1. SELECT COUNT(*) returns the count of all records in the result set regardless of NULL values.
2. SELECT COUNT(column_name) returns the count of records containing non-NULL values of the specified column.