COUNT IN ORACLE - Online Tech Support
  Home
 

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;

online tech support online computer help computer technician computer problems computer oracle count oracle count in oracle sql count oracle sql count in oracle sql database sql oracle sum oracle sum in oracle sql sum oracle sql sum in oracle sql retirement planning retirement

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;

online tech support online computer help computer technician computer problems computer oracle count oracle count in oracle sql count oracle sql count in oracle sql database sql oracle sum oracle sum in oracle sql sum oracle sql sum in oracle sql retirement planning retirement

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;

online tech support online computer help computer technician computer problems computer oracle count oracle count in oracle sql count oracle sql count in oracle sql database sql oracle sum oracle sum in oracle sql sum oracle sql sum in oracle sql retirement planning retirement

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;

online tech support online computer help computer technician computer problems computer oracle count oracle count in oracle sql count oracle sql count in oracle sql database sql oracle sum oracle sum in oracle sql sum oracle sql sum in oracle sql retirement planning retirement

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;

online tech support online computer help computer technician computer problems computer oracle count oracle count in oracle sql count oracle sql count in oracle sql database sql oracle sum oracle sum in oracle sql sum oracle sql sum in oracle sql retirement planning retirement

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;

online tech support online computer help computer technician computer problems computer oracle count oracle count in oracle sql count oracle sql count in oracle sql database sql oracle sum oracle sum in oracle sql sum oracle sql sum in oracle sql retirement planning retirement

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;

online tech support online computer help computer technician computer problems computer oracle count oracle count in oracle sql count oracle sql count in oracle sql database sql oracle sum oracle sum in oracle sql sum oracle sql sum in oracle sql retirement planning retirement

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

 

This Online Tech Support tutorial page is based on examples to make it easier to follow. Function Oracle REGR_COUNT allows counting the non-null number pairs and to fit them to the regression line. The REGR_COUNT function returns an integer value. The definition in short is that you can count two number pairs where values are not empty. The Oracle Count function allows counting only one number group but with REGR_COUNT you can do with 2 number groups and keep your SQL query smaller and simpler. The Oracle REGR_COUNT syntax of an aggregate function is following:

REGR_COUNT(<number>,<number>)

And the next syntax is the REGR_COUNT analytic function. This version does calculate on-fly and doesn’t need Group By clause.

REGR_COUNT(<number>,<number>) OVER ([<analytic_clause>])

To go through the examples with Oracle REGR_COUNT we will need some lines and a table. To save the time we will do it all dynamically generating the rows using the Oracle Dual table and CONNECT BY clause. All you have to do is copy and paste the SQL query and all the following rows will appear on your screen.

 SELECT MOD(ROWNUM,4) product_Id,
         DECODE(ROWNUM,2,NULL,ROWNUM) AS Bought_From_Id,
         DECODE(rownum,8,NULL,rownum) AS Sold_To_Id
    FROM dual
 CONNECT BY ROWNUM < 11;

online tech support online computer help computer technician computer problems computer oracle regr_count oracle regr_count in oracle sql regr_count oracle sql regr_count in oracle sql database sql retirement planning retirement

The SQL output describes product movements in a warehouse. The first column is a PRODUCT_ID it is an ID value from the PRODUCTS table. For testing purpose we left in zero values that are not so common in a real system. The second column BOUGHT_FROM_ID is an ID column that points to source from where the product is bought. The third column describes the location we did sell the product to. Take a look at the row there is a source missing on the second line and we haven’t sold a product on the 7th line (Product_Id is 0 and Bought_From_Id is 8). The meaning of this data doesn’t play much role for those examples but this description is done do give more sense to the numbers.

The first example is done with Oracle Count and the query above. Take a look at the output it returned 4 product Ids and the function did count 3,3,2,2 rows. The output is correct if you would need to count them only.

 SELECT PRODUCT_ID, COUNT(*)
  FROM (
    SELECT MOD(ROWNUM,4) PRODUCT_ID,
           DECODE(ROWNUM,2,NULL,ROWNUM) AS BOUGHT_FROM_ID,
           DECODE(ROWNUM,8,NULL,ROWNUM) AS SOLD_TO_ID
      FROM DUAL
   CONNECT BY ROWNUM < 11)
  GROUP BY product_id;

online tech support online computer help computer technician computer problems computer oracle regr_count oracle regr_count in oracle sql regr_count oracle sql regr_count in oracle sql database sql retirement planning retirement

The same query as the last one only with the Oracle REGR_COUNT function returns 4 product Id lines but the count returns 3,2,2,1. The Oracle Count has star (*) in the function and that condition doesn’t check anything beside the product Id value, so the function returned all available rows per product Id. When using the Oracle REGR_COUNT we need to declare the columns in the function and the REGR_COUNT function counts only non-null number pairs. On the first query you could see 2 rows where one of the values was NULL – those rows are left out from the count and that made the difference in output.

 SELECT PRODUCT_ID, REGR_COUNT(BOUGHT_FROM_ID, SOLD_TO_ID)
  FROM (
    SELECT MOD(ROWNUM,4) PRODUCT_ID,
           DECODE(ROWNUM,2,NULL,ROWNUM) AS BOUGHT_FROM_ID,
           DECODE(ROWNUM,8,NULL,ROWNUM) AS SOLD_TO_ID
      FROM DUAL
   CONNECT BY ROWNUM < 11)
  GROUP BY PRODUCT_ID;

online tech support online computer help computer technician computer problems computer oracle regr_count oracle regr_count in oracle sql regr_count oracle sql regr_count in oracle sql database sql retirement planning retirement

To see how will work the Oracle Count when you do apply on every column separately take a look at the query below. The query demonstrates that REGR_COUNT works as Oracle Count only it does the same per pair.

 SELECT PRODUCT_ID, COUNT(BOUGHT_FROM_ID), COUNT(SOLD_TO_ID)
  FROM (
    SELECT MOD(ROWNUM,4) PRODUCT_ID,
           DECODE(ROWNUM,2,NULL,ROWNUM) AS BOUGHT_FROM_ID,
           DECODE(ROWNUM,8,NULL,ROWNUM) AS SOLD_TO_ID
      FROM DUAL
   CONNECT BY ROWNUM < 11)
  GROUP BY PRODUCT_ID;

online tech support online computer help computer technician computer problems computer oracle regr_count oracle regr_count in oracle sql regr_count oracle sql regr_count in oracle sql database sql retirement planning retirement

The output above shows on the second column COUNT(BOUGH_FROM_ID) has left out a row for the second line product with Id 2. The third column has missing row for Product_Id 0. The both rows were left out because they had null values in it and that proves the both functions are working with the same logic.

The 5th query is done using Oracle Count and using the additional restriction the WHERE clause we can get the same output as with Oracle REGR_COUNT. The down side for this is that the query is much more complicated now.

 SELECT PRODUCT_ID, COUNT(*)
  FROM (
    SELECT MOD(ROWNUM,4) PRODUCT_ID,
           DECODE(ROWNUM,2,NULL,ROWNUM) AS BOUGHT_FROM_ID,
           DECODE(ROWNUM,8,NULL,ROWNUM) AS SOLD_TO_ID
      FROM DUAL
   CONNECT BY ROWNUM < 11)
  WHERE BOUGHT_FROM_ID IS NOT NULL 
    AND SOLD_TO_ID IS NOT NULL
  GROUP BY PRODUCT_ID;

online tech support online computer help computer technician computer problems computer oracle regr_count oracle regr_count in oracle sql regr_count oracle sql regr_count in oracle sql database sql retirement planning retirement

The summary would be that Oracle REGR_COUNT makes your SQL query smaller but there is always work around to get the same result using Oracle Count function.



See Also:
Oracle Select Oracle Count Oracle Group By Oracle Having Online Tech Support Home