SUM IN ORACLE SQL - Online Tech Support
  Home
 

This Online Tech Support page is based on examples to be easier to follow. The Oracle Sum Function sums the numbers and returns a result. The Sum function take account all not null values and ignores the null values. The Oracle SUM syntax of the aggregate function is following:

SUM(ALL <number>)
SUM(DISTINCT <number>)
SUM(<number>)

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

SUM(ALL <number>) OVER ([<analytic_clause>])
SUM(DISTINCT <number>) OVER ([<analytic_clause>])
SUM(<number>) OVER ([<analytic_clause>])

The first example is the most simple SUM and there is only one row is number “1” and since there is no more numbers the output returns number “1“. The function can be quite simple without extra columns you don’t need the GROUP BY clause.

 SELECT SUM(1) 
     FROM DUAL;

online tech support online computer help computer technician computer problems computer oracle sum oracle sum in oracle sql sum oracle sql sum in oracle sql database sql retirement planning retirement ORA-00937: not a single-group group function

The next Select statement will show us what happens when the query didn’t return any row. Take a look at the following SQL query there WHERE statement has restriction “1 = 2” and this equation will never be true so the query wouldn’t return a row. The query output return “(null)” because no numbers were to sum but Oracle SUM never returns an error NO_DATA_FOUND either Oracle COUNT, or MIN, or MAX and any other aggregate function.

 SELECT SUM(1) 
   FROM DUAL
  WHERE 1 = 2;

online tech support online computer help computer technician computer problems computer oracle sum oracle sum in oracle sql sum oracle sql sum in oracle sql database sql retirement planning retirement ORA-00937: not a single-group group function

To not have any lines returned you are still able to do some calculation with the NULL value you have to only replace it using Oracle NVL or NVL2 functions with any option you may need and your function wouldn’t fail because of the NULL value. The following example has the NVL function replacing the NULL value with zero (0) and the SQL output has number “0” for no rows.

 SELECT NVL(SUM(1),0) 
     FROM DUAL
    WHERE 1 = 2;

online tech support online computer help computer technician computer problems computer oracle sum oracle sum in oracle sql sum oracle sql sum in oracle sql database sql retirement planning retirement ORA-00937: not a single-group group function

To continue with more complex example we would need some data and to avoid making this examples to complicated we will use the Oracle DUAL table and the CONNECT BY clause to generate 10 lines.

 SELECT ROWNUM
   FROM DUAL
CONNECT BY ROWNUM < 11;

online tech support online computer help computer technician computer problems computer oracle sum oracle sum in oracle sql sum oracle sql sum in oracle sql database sql retirement planning retirement ORA-00937: not a single-group group function

The sixth SQL query will sum all 10 rows above and the returned number is “55” (1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 + 10 = 55). The result has been returned on one line as you can see on the following picture.

 SELECT SUM(ROWNUM)
    FROM DUAL
 CONNECT BY ROWNUM < 11;

online tech support online computer help computer technician computer problems computer oracle sum oracle sum in oracle sql sum oracle sql sum in oracle sql database sql retirement planning retirement ORA-00937: not a single-group group function

The same query above is done using Oracle SUM analytic function in above and to make the purpose more clear we did add the Oracle ROWNUM column as the first one. The “55” sum value is repeating over all rows.

 SELECT ROWNUM, SUM(ROWNUM) OVER ()
    FROM DUAL
 CONNECT BY ROWNUM < 11;

online tech support online computer help computer technician computer problems computer oracle sum oracle sum in oracle sql sum oracle sql sum in oracle sql database sql retirement planning retirement ORA-00937: not a single-group group function

To make the analytic function more complex we did apply clause “order by rownum asc” on the function and the result become now calculating the sum per line. To get the second column value add to the first column the previous row second column number and it makes the second column’s value. For example the 4th row has first column’s value “4” and add on it the 3rd’s row second column value what is “6“. The 4 + 6 = 10 and this is the 4th row second column’s number. The last value is “55” as on the last examples. This way written query let’s you to follow up the calculation when it’s very long one and not as this example or you can use this query to make your report look more detailed.

 SELECT ROWNUM, SUM(ROWNUM) OVER (order by rownum asc)
    FROM DUAL
 CONNECT BY ROWNUM < 11;

online tech support online computer help computer technician computer problems computer oracle sum oracle sum in oracle sql sum oracle sql sum in oracle sql database sql retirement planning retirement ORA-00937: not a single-group group function

You can use the analytic function to make the calculation appear reverse way as with clause “ORDER BY rownum DESC“. The rows are starting on the second column with “55” and the lowest row has the last number “10” left on it.

 SELECT ROWNUM, SUM(ROWNUM) OVER (ORDER BY rownum DESC)
    FROM DUAL
 CONNECT BY ROWNUM < 11
 ORDER BY 1 ASC;

online tech support online computer help computer technician computer problems computer oracle sum oracle sum in oracle sql sum oracle sql sum in oracle sql database sql retirement planning retirement ORA-00937: not a single-group group function

Important To Know:
Let’s try the same example above with Oracle SUM aggregate function and as you see on the following output instead of value “55” we got a error “ORA-00937: not a single-group group function“. The reason is that all columns that are not using any aggregate function needs to be added into GROUP BY clause or the SQL statement will fail.

 SELECT ROWNUM, SUM(ROWNUM)
    FROM DUAL
 CONNECT BY ROWNUM < 11;

online tech support online computer help computer technician computer problems computer oracle sum oracle sum in oracle sql sum oracle sql sum in oracle sql database sql retirement planning retirement ORA-00937: not a single-group group function

The next query is almost the same as the last one with only one exception we do have the ROWNUM function in the GROUP BY clause and the output is following:

 SELECT ROWNUM, SUM(ROWNUM)
    FROM DUAL
 CONNECT BY ROWNUM < 11
 GROUP BY ROWNUM;

online tech support online computer help computer technician computer problems computer oracle sum oracle sum in oracle sql sum oracle sql sum in oracle sql database sql retirement planning retirement ORA-00937: not a single-group group function

The query output didn’t make any sum because the ROWNUM value per line is unique and it didn’t create any value groups. To get the ROWNUM value summarised take a look at the following SQL query. There we are suing a DUMMY column which comes along with the Oracle Dual table. The only column that the table has. The query output character “X” in dummy column and the column has added to the GROUP BY clause and the sum is “55“.

 SELECT DUMMY, SUM(ROWNUM)
    FROM DUAL 
 CONNECT BY ROWNUM < 11
 GROUP BY DUMMY;

online tech support online computer help computer technician computer problems computer oracle sum oracle sum in oracle sql sum oracle sql sum in oracle sql database sql retirement planning retirement ORA-00937: not a single-group group function

To see what has the Oracle DUAL table we did add the following query so you can try it by yourself and the picture with the query result.

 SELECT * 
    FROM DUAL;

online tech support online computer help computer technician computer problems computer oracle sum oracle sum in oracle sql sum oracle sql sum in oracle sql database sql retirement planning retirement ORA-00937: not a single-group group function

The star (“*”) character returns all column from the Dual table and we don’t have any restriction on the query so there isn’t any WHERE keyword with a statement. The SQL query returned one line with one column DUMMY and it has value “X” in it. Since there is no more values beside this “X” the last SUM example returned only 1 row with sum “55“.



See Also:
Oracle Select Oracle ROWNUM Oracle NVL Oracle NVL2 Online Tech Support Home

 

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