SUM is one of the vital Numeric/Math functions of Oracle. It is used to get the SUM of an expression. The SUM function is supported in the various versions of the Oracle/PLSQL, including, Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i and Oracle 8i.
Syntax 1: To calculate simple SUM.
SELECT SUM (aggregate_expression) FROM tables WHERE conditions;
Syntax 2: To calculate SUM and grouping the results by one or more columns.
SELECT expression1, expression2, ... expression_n, SUM(aggregate_expression) FROM tables WHERE conditions GROUP BY expression1, expression2, ... expression_n;
Parameters: expression1, expression2, … expression_n: It is used to specify the expressions to be included in the GROUP BY clause but is not encapsulated in the SUM function. aggregate_expression: It is used to specify the column or expression that will be summed. tables: It is used to specify the tables to retrieve the records from. conditions: It is an optional parameter which is used to specify the conditions that must be met for selection.
Example 1:
SELECT SUM(marks) AS "SUM Marks" FROM students WHERE student_marks > 150; |
Explanation: Here we will get the SUM of the marks for the student_marks greater than 150, with a field name “SUM Marks”.
Example 2:
SELECT SUM( DISTINCT marks) AS "SUM Marks" FROM students WHERE student_marks > 150; |
Explanation: Here we will get the SUM of the marks for the student_marks greater than 150, with a field name “SUM Marks”, but no two equal student_marks will be Summed twice, since we are using the DISTINCT clause.
Example 3:
SELECT class, SUM(marks) AS "SUM Marks" FROM students GROUP BY class; |
Explanation: Here we will get the name of the class and the SUM of the marks in the associated class, since we are using the GROUP BY clause. We will get the SUM of the marks for the student_marks greater than 150, with a field name “SUM Marks”,
Example 4:
SELECT SUM(marks + 20) AS "SUM Marks" FROM students |
Explanation: Here we are using a formula to get the SUM of the marks for the students.
