The Oracle 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 Oracle COUNT Function
The purpose of the COUNT function is to count the number of rows returned in a SELECT statement.
The syntax of the Oracle 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(*) OVER (analytic_clause)
COUNT(DISTINCT expression) OVER (analytic_clause)
COUNT(ALL expression) OVER (analytic_clause)
You can use this function as an aggregate or analytical function, which I’ll explain shortly.
The parameters of the 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 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 all rows, including duplicates and nulls.
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.
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)
SELECT DISTINCT COUNT(expression)
Do I Need To Use GROUP BY with COUNT?
Yes, you do, but only if you are selecting more than the COUNT column.
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 Oracle (someone had told me it was true).
The short answer is no, it is not faster. COUNT(1) and COUNT(*) are exactly the same.
Tom Kyte (from AskTom) has confirmed on many occasions that they are the same, as mentioned in this post here.
- COUNT(*) is the correct way to write it.
- COUNT(1) is optimised to be COUNT(*) internally.
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 a certain criteria for COUNT?
SELECT fees_paid, COUNT(*) FROM student GROUP BY fees_paid;
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?
ORA-00934: group function is not allowed here
This error is expected. 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;
This shows that there are two students that have paid 150 in fees.
Examples of the COUNT Function
Here are some examples of the Oracle 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;
This example uses a basic COUNT(*) function.
SELECT COUNT(*) FROM student;
The result is 10 because there are 10 records in the table.
This example counts the unique values for the gender column
SELECT COUNT(DISTINCT gender) FROM student;
The result is 2 as there are 2 unique values.
This example uses the COUNT and DISTINCT in a different order.
SELECT DISTINCT COUNT(gender) FROM student;
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.
This example uses a column and the COUNT function.
SELECT gender, count(*) FROM student GROUP BY gender;
You can see that there are 6 students with an M value and 4 students with an F value.
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;
The result is 8, because two students have less than 100 in fees_paid.
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;
You can see there are 3 with the value of F and 5 with the value of M.
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.
Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!
Image courtesy of digitalart / FreeDigitalPhotos.net