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 tabletable
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.
![SQL COUNT() Function Example How to use COUNT() Function in SQL](/sites/tutorial2program/files/sql-count.png)
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.
![SQL COUNT() example with alias How to use alias in SQL with COUNT() function](/sites/tutorial2program/files/sql-count-alias.png)
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](/sites/tutorial2program/files/sql-count-using-column.png)
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.
![SQL COUNT) Function with WHERE Clause How to use COUNT() with WHERE in SQL](/sites/tutorial2program/files/sql-count-where-updated.png)
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 DISTINCT and COUNT used together to count unique values](/sites/tutorial2program/files/sql-select-count-distinct.png)
More on SQL COUNT()
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.
![SQL COUNT() Function with GROUP BY How to use COUNT() Function with GROUP BY in SQL](/sites/tutorial2program/files/sql-count-group-by.png)
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