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 w3wiki table where the rank is less than 4.
In this example, we are going to find the sum of scores in the w3wiki 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 w3wiki) 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 w3wiki table for even ranks.
In this example, we will calculate the sum of scores of the table w3wiki 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 w3wiki) 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 w3wiki table for ranks in the range 2-4.
In this example, we are going to calculate the sum of scores from w3wiki 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 w3wiki
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.
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.