COUNT() function returns the number of records returned by a query.
-- returns the number of values in the Orders table SELECT COUNT(*) FROM Orders;
The syntax of the SQL
COUNT() function is:
SELECT COUNT(expression) FROM table_name;
*, column name or expression such as
table_nameis the name of the table
-- 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()
In the above example, the field name in the result set is
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;
Here, the field name
COUNT(*) is replaced by total_customers in the result set.
COUNT() With WHERE
We can use
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';
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,
-- count the unique countries in Customers table SELECT COUNT(DISTINCT country) FROM Customers;
Here, the SQL command returns the count of unique countries.
COUNT() With GROUP BY
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
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.
COUNT() With NULL Values
SELECT COUNT(*)returns the count of all records in the result set regardless of NULL values.
SELECT COUNT(column_name)returns the count of records containing non-NULL values of the specified column.