SQL COUNT() Function: How to Count the Number of Rows

The SQL COUNT()  function returns the number of rows in the result set.

Example

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

Run Code

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

Count() Syntax

The syntax of the SQL COUNT() function is:

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 whose number of rows is to be counted

For Example,

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

Run Code

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

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,

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

Run Code

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

Example: COUNT() in SQL with Alias

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';

Run Code

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

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;

Run Code

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

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,

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

Run Code

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

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;

Run Code

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.

• This blog post was originally published at:https://www.programiz.com/

#sql  #database