CODEWITHSUNDEEP
sqloracle-database
Blaze Wingz
Blaze Wingz

Reputation: 73

How to limit rows to where SUM of a column equals to certain value and go to next row Oracle

Based on my question here, I have managed to get the answer.. but then a new problem arise that I need to display it like this :

Firstly, here is the studyplan table

enter image description here

So now, on the first run, it will display like this if I want to get the rows until SUM of Credit column equal to 18 :

enter image description here

But, then on the second run, I want it to be displayed like this if I want to get the rows until the SUM of Credit column equal to 21 :

enter image description here

How I want it to SUM the column at the next row? Do I have to make 2 SQL statement?

Here is the success code from the first run :

SELECT * FROM
(SELECT t.*,
SUM(t.credit) OVER (PARTITION BY t.matricsno ORDER BY t.sem, t.subjectcode)
AS credit_sum
FROM studyplan t)
WHERE credit_sum <= 17 AND matricsno = 'D031310087';

Thank you for your response and time.

Here is the link, How to limit rows to where SUM of a column equals to certain value in Oracle

Upvotes: 1

Views: 674

Answers (1)

sstan
sstan

Reputation: 36533

It's a bit of an odd requirement. But, yes, if you want 2 different result sets, you'll need separate 2 SQL statements. But for each additional statement, you would only need to tweak your condition on credit_sum.

For instance, if for your 1st query you want to get the rows up to when the credit sum reaches 18, you would do:

select *
  from (select t.*,
               sum(t.credit) over (order by t.sem, t.subjectcode) as credit_sum
          from studyplan t
         where t.matricsno = 'D031310087')
 where credit_sum <= 18
 order by sem, subjectcode

For your 2nd query, you say you want the rows where the credit sum reaches 21, but ignoring the rows returned by the 1st query. Another way to express that requirement is to return the rows for which the cumulative credit sum is between 19 and 39 (inclusive). So then, it simply becomes a matter of modifying the filter on credit_sum to use a between condition:

select *
  from (select t.*,
               sum(t.credit) over (order by t.sem, t.subjectcode) as credit_sum
          from studyplan t
         where t.matricsno = 'D031310087')
 where credit_sum between 19 and 39
 order by sem, subjectcode

Upvotes: 1

Related Questions