SQL COUNT()

In this tutorial, we'll learn about the SQL COUNT() function with the help of various examples.

The COUNT() function returns the number of rows in the result set. For example,

SELECT COUNT(*)
FROM Customers;

Here, the SQL command count rows and returns the total number of rows of the Customers table.

How to use COUNT() Function in SQL
Example: SQL COUNT() Function

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,

SELECT COUNT(*) AS total_customers
FROM Customers;

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

How to use alias in SQL with COUNT() function
Example: COUNT() in SQL with Alias

COUNT() with WHERE

Let's take an example.

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.

How to use COUNT() with WHERE in SQL
Example: SQL COUNT() Function with WHERE

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,

SELECT COUNT(DISTINCT country)
FROM Customers;

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

DISTINCT and COUNT used together to count unique values
Example: Counting 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,

SELECT country, COUNT(*) AS customers
FROM Customers
GROUP BY country;

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

How to use COUNT() Function with GROUP BY in SQL
Example: SQL COUNT() Function with GROUP BY

COUNT() With HAVING Clause

Let's take an example,

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.

To learn more, visit SQL HAVING Clause.


COUNT() With NULL Values

  1. SELECT COUNT(*) returns the count of all records in the result set regardless of NULL values.
  2. SELECT COUNT(attribute) returns the count of records containing non-NULL values of the specified column.
Did you find this article helpful?