Conditional Summation in PL/SQL

Conditional Summation is a process of calculating a sum based on some specific criteria or condition. Calculating "conditional summation" can have lots of use cases. In financial sectors, it allows us to calculate the total or average of a specific category. Similarly in educational sectors, conditional summation allows us to analyze students' marks for a particular subject for a year.

In logistics sectors, it can allow us to calculate transportation costs for a particular sector. There are many more use cases to calculate a conditional summation.

In this article, we are going to compute "Conditional Summation" in PL/SQL. We will deep dive into some real-life examples along with their explanations.

Conditional Summation

• Conditional Summation is a process of selectively adding some values based on some specified conditions or some specific criteria. Through conditional summation, we can perform a more detailed analysis by focusing on subsets of data, rather than complete data itself.
  
• In PL/SQL, we can achieve conditional summation can be achieved through a WHERE clause or IF statement. We can also achieve it through CASE statements in some of the cases.

Let's Set Up an Environment

To understand Conditional Summation in PL/SQL we need a table on which we will perform various operations and queries. Here we will consider a table called geeksforgeeks which contains id, name, score, and rank as Columns.

Examples of Conditional Summation in PL/SQL

In this, we will see various examples related to conditional summation in PL/SQL. We will cover all the basics to intermediate-level examples with clear and concise examples.

Example 1: Calculating the sum of scores in the Geeksforgeeks table where the rank is less than 4.

In this example, we are going to find the sum of scores in the Geeksforgeeks table where the rank is less than 4. We will use the IF statement in PL/SQL to achieve our task. We will specify our condition in the IF block and perform our operations. Let's see the query for a clear understanding.

Query:

DECLARE
    v_sum NUMBER := 0;
BEGIN
    FOR i IN (SELECT score,rank FROM geeksforgeeks) LOOP
        IF i.rank < 4 THEN
            v_sum := v_sum + i.score;
        END IF;
    END LOOP;
    
    DBMS_OUTPUT.PUT_LINE('Sum of scores with rank less than 4: ' || v_sum);
END;

Output:

Explanation: In the above query, we have first declared our variable which will store the value of the sum of scores. Then we will iterate through each row of our table. Then, through the IF statement, we will check if the rank is less than 4, if it do, then we will add it to our sum and store it in our defined variable. At the end, we will display our results. You can refer to the output image for more clear understanding.

Example 2: Calculating the sum of scores in the Geeksforgeeks table for even ranks.

In this example, we will calculate the sum of scores of the table geeksforgeeks where ranks are even. Likewise in the previous example, we are going to use the IF statement to perform our tasks. Let's take a look into the query for more clear understanding.

Query:

DECLARE
    v_sum NUMBER := 0;
BEGIN
    FOR i IN (SELECT score,rank FROM geeksforgeeks) LOOP
        IF MOD(i.rank, 2) = 0 THEN
            v_sum := v_sum + i.score;
        END IF;
    END LOOP;
    
    DBMS_OUTPUT.PUT_LINE('Sum of scores for even ranks: ' || v_sum);
END;

Output:

Explanation: In the above query, we have performed similar kinds of operations as we did in the previous example. The only difference lies in the IF block. In the IF statement, we have specified that if the rank%2 is equal to 0, then we will perform our operations. In our table, there are two even ranks; they are 2 and 4. Rank 2 has a score of 500, and rank 5 has a score of 490. Adding up both of these values gives us 990. This is what we have in the output block.

Example 3: Calculating the sum of scores in the Geeksforgeeks table for ranks in the range 2-4.

In this example, we are going to calculate the sum of scores from geeksforgeeks table where ranks lie in the range 2-4 (2 and 4 inclusive). Unlike in the previous two examples, we are going to use the WHERE clause for setting our condition. Let's take a look into the query for more clear understanding.

Query:

DECLARE
    v_score NUMBER := 0;
BEGIN
    SELECT SUM(score)
    INTO v_score
    FROM geeksforgeeks
    WHERE rank > 1 AND rank < 5;
    
    DBMS_OUTPUT.PUT_LINE('Sum of score for range 2-4: ' || v_score);
END;

Output:

Explanation: In this query, we have used the WHERE clause to form our query. We have first defined our variable which will hold our value of summation of score for the range 2-4. We have also used the SUM() function to sum up the values. Then, we will store this value into our variable and finally we will display our result. You can have a look into the output image for more clear understanding.

Conclusion

Conditional Summation is a process of selectively adding values based on some specified criteria. Computing conditional summation has lots of real world use cases. In financial sectors, we can compute total of a specified category. In education sectors, we can calculate total score of subject(s) for an academic year of a student. So we can see that, there are lots of use cases of computing conditional summation. In PL/SQL, we can use WHERE clause, IF statement to compute conditional summation. We have also seen examples related to it. We have covered all the basic syntax with some good example. Now you can write queries related to it with ease.