SQL COUNT()

The SQL COUNT() function returns the number of records returned by a query.

Example

-- returns the count of rows in the Orders table

SELECT COUNT(*)
FROM Orders;

Here, the above SQL command returns the count of rows in the Orders table.


COUNT() Syntax

SELECT COUNT(*)
FROM table;

Here,

  • COUNT() is the function to count the number of rows in a table
  • table is the name of the table

Example: SQL COUNT()

--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.

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

Aliases With COUNT()

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

--return the count of rows from the 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.

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

Example: Specify Column to Count

We can also specify a column name in COUNT() to only count the rows in that particular column.

Let's look at an example.

--returns the count of non-null values in the age column

SELECT COUNT(age)
FROM Customers;

Here, the above SQL query returns the count of non-null values in the age column.

SQL COUNT() Using a Column
SQL COUNT() Using a Column

Here, when we specify a column name instead of *, NULL values are not included in the count.


COUNT() With WHERE

We can use COUNT() with WHERE to count rows that match the given value.

-- count of customers who live in the UK

SELECT COUNT(country)
FROM Customers
WHERE country = 'UK';

Here, the SQL command returns the count of customers whose country is the 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,

-- count the unique countries in Customers table

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

More on SQL COUNT()

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.

How to use COUNT() Function with GROUP BY in SQL
Example: SQL COUNT() Function with GROUP BY
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:

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

Also Read

Did you find this article helpful?

Our premium learning platform, created with over a decade of experience and thousands of feedbacks.

Learn and improve your coding skills like never before.

Try Programiz PRO
  • Interactive Courses
  • Certificates
  • AI Help
  • 2000+ Challenges