The SQL COUNT function is one of the most common functions used by SQL developers. I use it on a daily basis. Learn more about the COUNT function in this article.
Purpose of the SQL COUNT Function
The aggregate COUNT function returns the count/number of non-null expressions evaluated in some result set
SQL Count Syntax
The syntax of the SQL COUNT function is:
COUNT ( [ * | [ DISTINCT | ALL ] expression) [ over (analytic_clause) ]Now, there’s a lot of square brackets and conditions in that function. Basically, you can call it in a few ways:
COUNT(*)
COUNT(DISTINCT expression)
COUNT(ALL expression)
COUNT(*) OVER (analytic_clause)
COUNT(DISTINCT expression) OVER (analytic_clause)
COUNT(ALL expression) OVER (analytic_clause)There is an analytic version of this function, that is a more advanced concept and something we’ll explore in a future article. For now, we’ll just look at the COUNT aggregation function.
Parameters
The parameters of the SQL COUNT function are:
- expression (optional): This is the expression that is used to find out the number of records.
- analytic_clause (optional): This is used to specify the criteria for an analytical query.
A few things to note about this function:
- COUNT(*) is the most common way to use this function.
- Because COUNT is an aggregate function, any non-constant columns in the SELECT clause that are not aggregated need to be in the GROUP BY clause. See the examples section below for more information.
- If you specify the expression, then COUNT returns the number of rows where expression is not null. You can count all rows (using ALL), or distinct values of the expression (using DISTINCT).
- If you specify the asterisk character (*), then COUNT returns a count of all of the rows that matched the predicate, including duplicates and nulls, or a count in a given group of rows as specified by the group by clause.
So, COUNT can return a few different values (in highest to lowest order):
- COUNT(*) – all rows, including duplicates and nulls.
- COUNT(expression) – all rows excluding null.
- COUNT(DISTINCT expression) – all rows excluding duplicates and nulls.
The fact that COUNT(expr) excludes NULL values means you should be careful as to which column or expression you use in this function. Different expressions may return different results.
SQL Count Distinct: How Can I Count Distinct Rows?
To count distinct rows, first, you have to find out what is distinct about them. Is it the ID? A name? Something else?
Then, you use the COUNT function with the DISTINCT keyword inside the function.
It needs to be inside the function, not outside.
So, it should be:
SELECT COUNT(DISTINCT expression)And not:
SELECT DISTINCT COUNT(expression)Find out more here: How to Count Distinct Values in SQL
Example of SQL COUNT DISTINCT
For example, let’s say you had a product table that had a column called product_name. This table has 100 records in it, and some of the product names are the same as others.
A simple COUNT will show 100 records:
SELECT COUNT(product_name) FROM product;| COUNT(product_name) |
| 100 |
If you use DISTINCT then COUNT, you’ll get one row:
SELECT DISTINCT COUNT(product_name) FROM product;| COUNT(product_name) |
| 100 |
This is because the COUNT is performed first, which finds a value of 100. The DISTINCT is then performed on all of the different COUNT values. There’s only one (the value of 100), so it’s the only one shown.
To get a count of distinct values in SQL, put the DISTINCT inside the COUNT function.
SELECT COUNT(DISTINCT product_name) FROM product;| COUNT(DISTINCTproduct_name) |
| 85 |
This will show the number of distinct product names. In this example, we can see there are 85, which is the number we’re looking for.
Do I Need To Use GROUP BY with COUNT?
Yes, you do, but only if you are selecting non-aggregate expressions from the query source.
For example, this query would need a GROUP BY:
SELECT gender, count(*) FROM student;But, this query wouldn’t:
SELECT COUNT(gender) FROM student;
Is COUNT(1) Faster than COUNT(*)?
This is a common question and one that I actually believed when I started working with SQL (someone had told me it was true).
The short answer is no, it is not faster. COUNT(1) and COUNT(*) are exactly the same.
In Oracle, Tom Kyte (from AskTom) has confirmed on many occasions that they are the same, as mentioned in this post here.
In summary:
- COUNT(*) is the correct way to write it.
- COUNT(1) is optimised to be COUNT(*) internally (in Oracle).
So, you should never use COUNT(1).
How Can I Only Show Records That Meet a Criteria on the COUNT Function?
So, you’ve got a query that returns a value for COUNT.
How do you filter your result set to only show values that match certain criteria for COUNT?
SELECT fees_paid, COUNT(*)
FROM student
GROUP BY fees_paid;Result:
| FEES_PAID | COUNT(*) |
| 100 | 1 |
| 500 | 1 |
| 350 | 1 |
| 200 | 1 |
| 900 | 1 |
| 50 | 1 |
| 0 | 1 |
| 150 | 2 |
| 800 | 1 |
Let’s say you only want to see records where two or more students have paid the same number of fees.
You could try the WHERE clause.
SELECT fees_paid, COUNT(*)
FROM student
WHERE COUNT(*) > 1
GROUP BY fees_paid;Makes sense right?
If you run this query in Oracle:
ORA-00934: group function is not allowed here
This error is expected. You’ll get similar errors in SQL Server, MySQL, and PostgreSQL. It happens because of the WHERE clause.
The WHERE clause actually runs before the COUNT function. This is important because you can’t refer to the COUNT function in the WHERE clause, as the WHERE clause determines the count.
How can you get the result you want?
You use the HAVING clause.
The HAVING clause is similar to the WHERE clause, except it runs after the results are fetched. You can use this on the COUNT function.
SELECT fees_paid, COUNT(*)
FROM student
GROUP BY fees_paid
HAVING COUNT(*) > 1;Result:
| FEES_PAID | COUNT(*) |
| 150 | 2 |
This shows that there are two students that have paid 150 in fees.
Examples of the SQL COUNT Function
Here are some examples of the SQL COUNT function. I find that examples are the best way for me to learn about code, even with the explanation above.
Let’s take a look at our sample table first.
SELECT * FROM student;| STUDENT_ID | FIRST_NAME | LAST_NAME | FEES_PAID | GENDER |
| 1 | John | Smith | 200 | M |
| 2 | Susan | Johnson | 500 | F |
| 3 | Tom | Capper | 350 | M |
| 4 | Mark | Holloway | 100 | M |
| 5 | Steven | Webber | 0 | M |
| 6 | Julie | Armstrong | 150 | F |
| 7 | Michelle | Randall | 150 | F |
| 8 | Andrew | Cooper | 800 | M |
| 9 | Robert | Pickering | 900 | M |
| 10 | Tanya | Hall | 50 | F |
Example 1
This example uses a basic COUNT(*) function.
SELECT COUNT(*)
FROM student;Result:
| COUNT(*) |
| 10 |
The result is 10 because there are 10 records in the table.
Example 2
This example counts the unique values for the gender column
SELECT COUNT(DISTINCT gender)
FROM student;Result:
| COUNT(DISTINCTGENDER) |
| 2 |
The result is 2 as there are 2 unique values.
Example 3
This example uses the COUNT and DISTINCT in a different order.
SELECT DISTINCT COUNT(gender)
FROM student;Result:
| COUNT(GENDER) |
| 10 |
The result is 10, because the query actually performs the count of gender, then finds all distinct rows. Because the only row returned has a value of 10, it is already unique.
Example 4
This example uses a column and the COUNT function.
SELECT gender, count(*)
FROM student
GROUP BY gender;Result:
| GENDER | COUNT(*) |
| M | 6 |
| F | 4 |
You can see that there are 6 students with an M value and 4 students with an F value.
Example 5
This example finds the number of students who have more than or equal to 100 in fees_paid.
SELECT COUNT(*)
FROM student
WHERE fees_paid >= 100;Result:
| COUNT(*) |
| 8 |
The result is 8, because two students have less than 100 in fees_paid.
Example 6
This example finds the number of students that have paid more than or equal to 100 in fees and splits them by gender.
SELECT gender, count(*)
FROM student
WHERE fees_paid >= 100
GROUP BY gender;Result:
| GENDER | COUNT(*) |
| M | 5 |
| F | 3 |
You can see there are 3 with the value of F and 5 with the value of M.
Similar Functions
Some functions which are similar to the COUNT function are:
- SUM – This function adds up all of the values specified in a query.
- AVG – This function finds the average of the values specified in a query.
You can find a full list of Oracle SQL functions here. Lists of other database functions are coming soon.