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

    group by based on sum of a column

    960870
      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
          sb92075
          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
            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 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
            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 ADAMS 4100
            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
            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 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
              960870
              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