SQL COUNT()

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.

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.

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.

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.

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.

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.