Home » SQL & PL/SQL » SQL & PL/SQL » SUM function..??
SUM function..?? [message #231058] Fri, 13 April 2007 16:55 Go to next message
vamsik
Messages: 15
Registered: February 2007
Junior Member
Hi Frds,

I have a data in a table like this:-

id name sal
1000 abcd 600
1000 500
2000 efgh 1000

Now i have to write a select statement to retrieve the data like this:-
Output should be:
id name sal
1000 abcd 1100 --(500+600)
2000 efgh 1000

Please guide me.

Thanks in advance.
Vamsi.

[Updated on: Fri, 13 April 2007 16:57]

Report message to a moderator

Re: SUM function..?? [message #231061 is a reply to message #231058] Fri, 13 April 2007 18:43 Go to previous messageGo to next message
SnippetyJoe
Messages: 63
Registered: March 2007
Location: Toronto, Canada
Member
I'm not sure I understand why the 1100 term is used for id 1000 but not 2000. Regardless, the following query may provide enough tools to help you come up with the right solution.

create table t ( id number, name varchar2(10), n number );
insert into t values ( 1000, 'abcd', 600 );
insert into t values ( 1000, null, 500 );
insert into t values ( 2000, 'efgh', 1000 );

select 
  id,
  min( name ) as name,
  case id when 1000 then 1100 else 0 end +
    ( case id when 1000 then -1 else 1 end * sum( n ) )
    as sal
from t
group by id ;

        ID NAME              SAL
---------- ---------- ----------
      1000 abcd                0
      2000 efgh             1000

drop table t;


--
Joe Fuda
http://www.sqlsnippets.com/

[Updated on: Fri, 13 April 2007 18:45]

Report message to a moderator

Re: SUM function..?? [message #231089 is a reply to message #231058] Sat, 14 April 2007 01:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select id, min(name) name, sum(n) n
  2  from t
  3  group by id
  4  order by id
  5  /
        ID NAME                N
---------- ---------- ----------
      1000 abcd             1100
      2000 efgh             1000

2 rows selected.

Regards
Michel
Re: SUM function..?? [message #231112 is a reply to message #231058] Sat, 14 April 2007 08:24 Go to previous messageGo to next message
SnippetyJoe
Messages: 63
Registered: March 2007
Location: Toronto, Canada
Member
Ah, I see I read the original query wrong. I thought I saw "1100 - (500+600)", not "1100 --(500+600)". One less "-" makes a big difference (groan, excuse the pun). Please ignore my last post.
Re: SUM function..?? [message #231122 is a reply to message #231058] Sat, 14 April 2007 12:28 Go to previous message
vamsik
Messages: 15
Registered: February 2007
Junior Member
Thank you very much friends.
Very simple solution with MIN function..(i tried in hectic ways..even i couldnt get the solution)..once again thanks frd's.
Previous Topic: compress table
Next Topic: select get
Goto Forum:
  


Current Time: Thu Dec 08 01:58:36 CST 2016

Total time taken to generate the page: 0.10315 seconds