This tutorial is based on examples to be easier to follow. The Oracle Count Function returns a number of rows returned by the SQL query. You can use the function with “*“, “ALL“, “DISTINCT“, or/and your own condition to set the Count function to return only required rows. The function returns always a value and it can be used as an aggregate or analytic function depends on the optional analytic clause has added to the function or not. The Oracle Count syntax is following:
COUNT(*) [OVER (<analytic_clause>)])
COUNT(ALL <condition>) [OVER (<analytic_clause>)])
COUNT(DISTINCT <condition>) [OVER (<analytic_clause>)])
COUNT(<condition>) [OVER (<analytic_clause>)])
When you are using the condition in the Oracle Count then the function counts only lines where the condition returns not null value.
To go through the examples we will need a table and some data. The easiest way is to use the DUAL table and generated some lines with Oracle Rownum and the hierarchical clause Connect By as in the following sample.
SELECT ROWNUM FROM DUAL CONNECT BY ROWNUM < 11;
The query above returns 10 rows with Rownum value between 1 and 10. This example will count all those 10 rows and we are doing it with the COUNT(*) way. Take a look at the following query.
SELECT COUNT(*) FROM DUAL CONNECT BY ROWNUM < 11;
The function above is used as an aggregate function so it returned the value as one row. The SQL output shows the count number 10 as we had the same amount of lines above.
The second example is demonstrating the Count function with your condition. The condition is ROWNUM function and since the function always returns a value per line and we do have 10 rows then the Count returns again number 10.
SELECT count(ROWNUM) FROM DUAL CONNECT BY ROWNUM < 11;
The following example shows why we cannot use Oracle SUM to summarise the amount of rows. Using Oracle SUM the amount gets summarised by the column’s value and not by the row’s amount as on the following output.
SELECT SUM(ROWNUM) FROM DUAL CONNECT BY ROWNUM < 11;
The number 55 came from summarising the following Rownum values over 10 lines:
1+2+3+4+5+6+7+8+9+10 = 55
And using the column value doesn’t return the amount of rows but a value inside them.
Now we’ll have a look what is different between an aggregate and analytic function. The examples above were aggregate functions and the following Select statement is written as an analytic function. The analytic function doesn’t group the values so we left the Rownum function as the first column and the second is Count and an empty analytic condition. The condition should be written into the “OVER” keyword. The current SQL runs the Oracle Count over all lines without any restriction.
SELECT rownum, COUNT(*) over () FROM DUAL CONNECT BY ROWNUM < 11;
The aggregate function returned the value as 1 row and the analytic function repeats the value on every row. The total amount 10 is written as second column per every row.
The analytic function can be run with restrictions or an extra clause and the fifth example has the Count function with order by condition in the analytic section. The clause sets to call the Oracle Count function on-fly per row and it only counts left rows depending on the current row. The output below has a count number decreasing per every row so you will know on every row how many rows are still left without manually counting them.
SELECT rownum, COUNT(*) over (order by rownum DESC) FROM DUAL CONNECT BY ROWNUM < 11 ORDER BY 1;
The next example is again the analytic function and this time the restriction is set per value groups. We are using lightly different source query were Rownum value has applied Oracle MOD function and the value is divided by 3. This column has set to the analytic function as a partition (please don’t confuse this with table partitions). The partitions are treated in Oracle Analytic function as value groups so the count is taken only per data group as the following example shows.
SELECT t.numbers,
COUNT(*) over (partition BY t.numbers)
FROM (SELECT mod(rownum,3) numbers
FROM DUAL
CONNECT BY ROWNUM < 11 ) t
ORDER BY t.numbers;
The SQL output above shows repeating Count values per data group. Take a look at the left column first where the three rows are number “0” and on the right side the count values shows 3. That does mean we do have “3” lines with number 0. The following number group is “1” – 4 rows and number “2” with 3 rows.
See Also:
Home Oracle Select