This content has been marked as final.
Show 3 replies

1. Re: group by based on sum of a column
sb92075 Sep 8, 2012 8:56 PM (in response to 960870)How do I ask a question on the forums?
SQL and PL/SQL FAQ 
2. Re: group by based on sum of a column
Frank Kulash Sep 8, 2012 11:05 PM (in response to 960870)Hi, Suresh,
Welcome to the forum!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).
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)
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
Did 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 25
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?
VHXA S1 37 52any 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, commtags. 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:
FROM scott.emp
ORDER BY deptno, ename
;
` DEPTNO ENAME SAL COMMproduces this output:
   
10 CLARK 2450
10 KING 5000
10 MILLER 1300
20 ADAMS 1100
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
` DEPTNO ENAME TOTAL_SAL TOTAL_COMMNow 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:
   
10 CLARK 7450
20 ADAMS 4100
20 JONES 5975
30 ALLEN 4450 300
30 JAMES 3700 1400
WITH got_analytics ASHere's one way to get those results:
(
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
START WITH r_num = 1
CONNECT BY r_num = PRIOR r_num + PRIOR cnt
AND deptno = PRIOR deptno
ORDER BY deptno, ename
;Briefly, how it works is that the subquery 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
960870 Sep 9, 2012 4:39 PM (in response to Frank Kulash)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