This discussion is archived
2 Replies Latest reply: Oct 30, 2012 7:39 AM by Frank Kulash RSS

GROUP by equal values

967659 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points