2 Replies Latest reply: Oct 30, 2012 9:39 AM by Frank Kulash RSS

    GROUP by equal values

    967659
      I have data
      Type, Month, Amount
      =========
      4, 1, 43333.33333
      4, 2, 43333.33333
      4, 3, 43333.33333
      8, 5, 16000
      8, 6, 16000
      8, 7, 16000
      8, 8, 16000
      4, 2, 100
      4, 3, 100
      and I want to group and SUM() equal amounts and find MIN(month) and MAX(month), so the output should look like
      Type, MIN(month), MAX(month), SUM(amount)
      =========
      4, 1, 3, 130000
      8, 5, 8, 64000
      4, 2, 3, 200
      How is it possible?
        • 1. Re: GROUP by equal values
          odie_63
          A simple GROUP-BY will do.
          What did you try that doesn't work?
          SQL> with sample_data (type, month, amount) as (
            2    select 4, 1, 43333.33333 from dual union all
            3    select 4, 2, 43333.33333 from dual union all
            4    select 4, 3, 43333.33333 from dual union all
            5    select 8, 5, 16000 from dual union all
            6    select 8, 6, 16000 from dual union all
            7    select 8, 7, 16000 from dual union all
            8    select 8, 8, 16000 from dual
            9  )
           10  select type, min(month), max(month), sum(amount)
           11  from sample_data
           12  group by type, amount
           13  ;
           
                TYPE MIN(MONTH) MAX(MONTH) SUM(AMOUNT)
          ---------- ---------- ---------- -----------
                   4          1          3 129999,9999
                   8          5          8       64000
           
          • 2. Re: GROUP by equal values
            Frank Kulash
            Hi,
            964656 wrote:
            I have data
            Month, Amount
            =========
            1, 43333.33333
            2, 43333.33333
            3, 43333.33333
            5, 16000
            6, 16000
            7, 16000
            8, 16000
            and I want to group and SUM() equal amounts and find MIN(month) and MAX(month), so the output should look like
            MIN(month), MAX(month), SUM(amount)
            =========
            1, 3, 130000
            5, 8, 64000
            How is it possible?
            You practially coded the whole thing already:
            SELECT       MIN (month)     AS min_month
            ,       MAX (month)     AS max_month
            ,       SUM (amount)     AS sum_amount
            FROM       table_x
            GROUP BY  amount
            ;
             

            I hope this answers your question.
            If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all tables involved, and also post the results you want from that data.
            Point out a few places where the query above is giving the wrong results, and explain, using specific examples, how you get those results from that data in those places.
            Always say which version of Oracle you're using (e.g., 11.2.0.2.0).
            See the forum FAQ {message:id=9360002}
            You'll get better answers faster if you always supply this information whenever you post a question.