Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2009
    Posts
    3

    Unanswered: Efficient way for sum of distinct columns

    All,

    I have a table structure like below.

    COL1 varchar2(10),
    COL2 varchar2(10),
    col3 varchar2(10),
    col4 varchar2(10),
    col5 number(10).

    My requirement is that "Aggregate by Col1, Col2, COl3, sum (col5) for distinct col4 values"

    This can be done using a laborious SQL like below.

    select col1,col2,col3,sum(col5)
    from
    (
    select distinct col1,col2,col3,col4,col5
    from tmp4
    )
    group by col1,col2,col3

    Is there an efficient way?. Using analytical functions?

    Appreciate your help.
    Last edited by giripc; 07-23-09 at 03:21.

  2. #2
    Join Date
    Feb 2009
    Posts
    62
    Why is this not just:
    Code:
    SELECT col_4,col_1,col_2,col_3,sum(col_5)
    FROM table
    GROUP BY col_4,col_1,col_2,col_3

  3. #3
    Join Date
    Feb 2009
    Posts
    62
    Here's a test case - I've removed col2 and col3 for simplicity. Tell us what the results that you expect back are.
    If this data doesn't match your requirements, feel free to post a test case of your own, as long as it includes CREATE TABLE and INSERT statementswt
    Code:
    create table test_026 (col_1  number ,col_4 number ,col_5 number);
    
    insert into test_026 values(1,1,1);
    insert into test_026 values(2,1,1.5);
    insert into test_026 values(3,1,2);
    
    insert into test_026 values(1,2,1);
    insert into test_026 values(2,2,3);
    insert into test_026 values(6,2,7);

  4. #4
    Join Date
    Jul 2009
    Posts
    3

    Sum of distinct query - Test case attached

    Thank you for looking into this. Here is the test case. I have added an additional row in the test. (

    create table test_026 (col_1 number ,col_4 number ,col_5 number);

    insert into test_026 values(1,1,1);
    insert into test_026 values(1,1,1);


    insert into test_026 values(1,2,1);
    insert into test_026 values(1,2,1);


    insert into test_026 values(2,1,1.5);
    insert into test_026 values(2,1,1.5);

    insert into test_026 values(2,2,3);
    insert into test_026 values(2,2,3);

    insert into test_026 values(3,1,2);
    insert into test_026 values(3,1,2);




    insert into test_026 values(6,2,7);
    insert into test_026 values(6,2,7);


    Here is the result i am looking for. I don't want col4 in the output result.

    col1, Sum(col5)
    ---- ----------
    1 2
    2 4.5
    3 2
    6 7

    For each col1, get distinct col4,col5. Sum col5.

    Below query will get the job done. But, it doesn't seem efficient.

    select col_1,sum(col_5)
    from
    (select distinct col_1,col_4,col_5
    from test_026
    )
    group by col_1;


    Appreciate your help.

    Regards,
    giri.
    Last edited by giripc; 07-23-09 at 10:06.

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by giripc
    For each col1, get distinct col4,col5. Sum col5.

    Below query will get the job done. But, it doesn't seem efficient.
    I guess you have to define "efficient" first.

    Does this "seem efficient"?
    Code:
    select col_1, s
    from
    (select col_1,col_4,sum(col_5) s
    from test_026
    group by col_1, col_4
    );
    ---
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    Feb 2009
    Posts
    62
    Efficient or not, it doesn't return the right results:
    Code:
    SQL> select col_1, s
      2  from
      3  (select col_1,col_4,sum(col_5) s
      4  from test_026
      5  group by col_1, col_4
      6  );
    
         COL_1          S
    ---------- ----------
             2          3
             1          2
             3          4
             6         14
             1          2
             2          6
    
    6 rows selected.

  7. #7
    Join Date
    Feb 2009
    Posts
    62
    You could do it this way:
    Code:
    select col_1
          ,sum(col_5)
    from  (select col_1
                 ,col_4
                 ,col_5
                 ,row_number() over (partition by col_1,col_4 order by null) rnum
           from   test_026)
    where rnum=1
    group by col_1;
    but at the end of the day, you've got two different grouping/sorting processes to perform, and I'd be suprised if any approach made a great deal of difference.
    <cue blindingly simple single pass solution that I've overlooked......>

  8. #8
    Join Date
    Jul 2009
    Posts
    3

    Thank you...

    Thank you for your input & analytical query. I'll run a trace and check this one.

    My mindset has been favoring analytical functions:-)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •