3 Replies Latest reply: Sep 9, 2012 9:39 PM by 960870

# group by based on sum of a column

dear all

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)

REGO S_NUM HRS QTY
==== ===== === ===
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 25
VHXA S1 37 52

any ideas how to write a pl/sql code for this ?

Thanks
Suresh Narasimha
• ###### 1. Re: group by based on sum of a column
How do I ask a question on the forums?
SQL and PL/SQL FAQ
• ###### 2. Re: group by based on sum of a column
Hi, Suresh,

Welcome to the forum!
user10742238 wrote:
dear all

i have a scenario that I think can't be achieved using SQL alone.
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 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 QTY
==== ===== === ===
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)
Did you mean 33, not 32?
VHXA S1 30 25
VHXA S1 37 52
So, 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?
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 \
`````` 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:``````
SELECT     deptno, ename, sal, comm
FROM     scott.emp
ORDER BY deptno, ename
;
``produces this output:``
` DEPTNO ENAME SAL COMM
---------- ---------- ---------- ----------
10 CLARK 2450
10 KING 5000
10 MILLER 1300

20 FORD 3000
20 JONES 2975
20 SCOTT 3000
20 SMITH 800

30 ALLEN 1600 300
30 BLAKE 2850
30 JAMES 950
30 MARTIN 1250 1400
30 TURNER 1500 0
30 WARD 1250 500
``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:``
` DEPTNO ENAME TOTAL_SAL TOTAL_COMM
---------- ---------- ---------- ----------
10 CLARK 7450

20 JONES 5975

30 ALLEN 4450 300
30 JAMES 3700 1400
``Here's one way to get those results:``
WITH     got_analytics     AS
(
SELECT     deptno, ename, sal, comm
,     ROW_NUMBER () OVER ( PARTITION BY deptno
ORDER BY      ename
)      AS r_num
,     SUM (sal) OVER ( PARTITION BY deptno
ORDER BY      ename
)      AS running_sal
FROM scott.emp
)
,     got_grps     AS
(
SELECT     l.deptno
,     l.ename
,     l.r_num
,     SUM (h.sal)     AS total_sal
,     SUM (h.comm)     AS total_comm
,     COUNT (*)     AS cnt
FROM     got_analytics     l
JOIN     got_analytics     h ON h.deptno = l.deptno
AND h.running_sal BETWEEN l.running_sal
AND l.running_sal + 3000
+ h.sal
- l.sal
GROUP BY l.deptno
,      l.ename
,     l.r_num
)
SELECT deptno, ename, total_sal, total_comm
FROM     got_grps
WHERE     total_sal     > 3000
CONNECT BY     r_num     = PRIOR r_num + PRIOR cnt
AND     deptno     = PRIOR deptno
ORDER BY deptno, ename
;
``````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.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    ``````
• ###### 3. Re: group by based on sum of a column
Thanks Frank!!

I'll try this as your solution gave me a fair idea.

My apologies for not providing sample scripts. Next time i'll do it :)

Regards
Suresh Narasimha