Thread: Efficient way for sum of distinct columns

1. Registered User
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?

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

2. Registered User
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. Registered User
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. Registered User
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;

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

5. :-)
Join Date
Jun 2003
Location
Posts
5,516
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
);

6. Registered User
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. Registered User
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. Registered User
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
•