user10742238 wrote:Sure you can do that in pure SQL. Below is a solution using CONNECT BY. Another approach involves MODEL, and yet another uses a recursive WITH clause (new in Oracle 11.2).
i have a scenario that I think can't be achieved using SQL alone.
I need to group by a column while sum of another column is > 25. here is some sample data, (assuming it is sorted descending by another date field)Which column has to add up to more that 25? It looks like hrs, but don't make people guess.
REGO S_NUM HRS QTYDid you mean 33, not 32?
==== ===== === ===
VHXB S1 9 15
VHXB S1 10 10
VHXB S1 7 25
VHXB S1 15 9
VHXB S1 18 18
VHXB S1 10 6
VHXB S1 12 24
VHXA S1 12 15
VHXA S1 18 10
VHXA S1 7 25
VHXA S1 12 9
VHXA S1 18 18
VHXA S1 11 6
VHXA S1 12 24
The above data should be grouped by REGO, S_NUM and the result should look like below,
VHXB S1 26 50 (first 3 rows since the sum(HRS is greater than 25))
VHXB S1 32 27 (next 2 rows)
VHXA S1 30 25So, if there's a group at the end of any rego/s_num section, and it totals 25 or less, you want to ignore that last group. Right?
VHXA S1 37 52
any ideas how to write a pl/sql code for this ?The previous answer is right. Whevenver you have a problem, post CREATE TABLE and INSERT statements for your sample data (relevant columns only: in this case, the DATE column that determines the order is relevant) and the results you want from that data, formatted, within \
SELECT deptno, ename, sal, comm
tags. Explain how you get those results from that data. If you can show the problem using commonly available tables (such as those in the scott or hr schemas) then you don't need to post any sample data; just the results and the explanation. Your problem is very similar to the following problem, based on the scott.emp table. The following query:
` DEPTNO ENAME SAL COMM
produces this output:
` DEPTNO ENAME TOTAL_SAL TOTAL_COMM
Now say we want to group consecutive rows (where "consecutive" means in order by ename, which we'll assume is unique within each deptno) such that each group has a total sal of over 3000. That is, we want these results:
WITH got_analytics AS
Here's one way to get those results:
Briefly, how it works is that the sub-query got_grps calculates, for each row, what the totals would be if a group started with that row. For example, in deptno=10, we know a group will start with CLARK, because taht's the first row, but we won't know if another group will begin with KING or MILLER. Got_grps assumes there will be such a group, and counts the number of rows in the group. Then, in the main query, we use that count to find where the second (and later) groups actually do begin. Always say which version of Oracle you're using. The query above works should work in Oracle 9.1 (and higher); I tested it using version 10.2.0.1.0.