SQL COUNT()

-- returns the count of rows in the Orders table

SELECT COUNT(*)
FROM Orders;

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.

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.

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.

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.

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.

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.

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

• SQL MIN() and MAX()
• SQL SUM() and AVERAGE()
• SQL COUNT() With DISTINCT