Home » SQL & PL/SQL » SQL & PL/SQL » find difference of two numbers in oracle sql
find difference of two numbers in oracle sql [message #406993] Mon, 08 June 2009 02:16 Go to next message
rtnataraj
Messages: 102
Registered: December 2008
Senior Member

select col1,sum(col2)
FROM landing
GROUP BY col1

I want to perform difference instead of sum in the given query.

is there any function available in oracle sql.
pls help me.

regards,
Nataraj.
Re: find difference of two numbers in oracle sql [message #406995 is a reply to message #406993] Mon, 08 June 2009 02:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Difference between what and what?
Sum is obvious as (A+B)+C=(A+(B+C) but difference what should it be: A-(B-C) or (A-B)-C?

Post a working Test case: create table and insert statements along with the result you want with these data.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: find difference of two numbers in oracle sql [message #407010 is a reply to message #406993] Mon, 08 June 2009 03:00 Go to previous messageGo to next message
rtnataraj
Messages: 102
Registered: December 2008
Senior Member

LANDING table contains

COL1 COL2

A 100
B 200
C 100
A 200


SELECT col1, SUM (col2)
FROM landing
GROUP BY col1;

returns below result:

COL1 SUM(COL2)

A 300
B 200
C 100


I want to perform difference instead of sum in the given query.


COL1 DIFF(COL2)

A 100
B 200
C 100


REGARDS,
NATARAJ

[Updated on: Mon, 08 June 2009 03:01]

Report message to a moderator

Re: find difference of two numbers in oracle sql [message #407012 is a reply to message #407010] Mon, 08 June 2009 03:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why A is 100 and not -100?

Regards
Michel
Re: find difference of two numbers in oracle sql [message #407013 is a reply to message #407012] Mon, 08 June 2009 03:03 Go to previous messageGo to next message
rtnataraj
Messages: 102
Registered: December 2008
Senior Member

I need absolute value.

regards,
Nataraj
Re: find difference of two numbers in oracle sql [message #407016 is a reply to message #407013] Mon, 08 June 2009 03:07 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What should happen if you have 3 values in the group of A - say your test data was:
CREATE TABLE test_213 (col_1 varchar2(10), col_2 number);

insert into test_213 values ('A',100);
insert into test_213 values ('A',200);
insert into test_213 values ('A',50);
insert into test_213 values ('B',200);
insert into test_213 values ('C',100);
then what should the result be?
Re: find difference of two numbers in oracle sql [message #407017 is a reply to message #407016] Mon, 08 June 2009 03:10 Go to previous messageGo to next message
rtnataraj
Messages: 102
Registered: December 2008
Senior Member

I confirm, this case will not occur in my scenario.

regards,
Nataraj
Re: find difference of two numbers in oracle sql [message #407018 is a reply to message #407017] Mon, 08 June 2009 03:19 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Now, why do you want a non null/zero value returning for groups B & C - there is no difference to calculate as there is only one row in each group, so the value returned should be 0 or null.
Re: find difference of two numbers in oracle sql [message #407020 is a reply to message #407018] Mon, 08 June 2009 03:23 Go to previous messageGo to next message
rtnataraj
Messages: 102
Registered: December 2008
Senior Member

Is it so?
then I want the value to be returned if there is only one row in each group.

As I said before, I need the below result.


COL1 DIFF(COL2)

A 100
B 200
C 100

regards,
Nataraj
Re: find difference of two numbers in oracle sql [message #407021 is a reply to message #407020] Mon, 08 June 2009 03:29 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Take a look at the LAG or LEAD functions.
Re: find difference of two numbers in oracle sql [message #407024 is a reply to message #407020] Mon, 08 June 2009 03:39 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
( AVG() - MIN() ) * 2

of course only if you have exactly 2 records (but that is because of the error in your logic JRowbottom pointed you to already).
That can be checked with CASE & COUNT

[Updated on: Mon, 08 June 2009 03:42]

Report message to a moderator

Re: find difference of two numbers in oracle sql [message #407026 is a reply to message #407024] Mon, 08 June 2009 03:47 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'd have gone with MAX() - MIN() but that still hits the same problem.

This is a strong contender for the 'Most arbitrary function' award.
Re: find difference of two numbers in oracle sql [message #407027 is a reply to message #406993] Mon, 08 June 2009 03:49 Go to previous messageGo to next message
karthick_arp
Messages: 13
Registered: February 2006
Location: hyderabad
Junior Member
SQL> with t
  2  as
  3  (
  4  select 'A' c1, 100 c2 from dual union all
  5  select 'B', 200 from dual union all
  6  select 'C', 100 from dual union all
  7  select 'A', 200 from dual
  8  )
  9  select c1, sum(decode(rno,1,1,-1)*c2) c2
 10    from (select row_number() over(partition by c1 order by c2 desc) rno,
 11            c1,
 12            c2
 13       from t)
 14   group by c1
 15  /

C         C2
- ----------
A        100
B        200
C        100
Re: find difference of two numbers in oracle sql [message #407029 is a reply to message #407026] Mon, 08 June 2009 03:58 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
JRowbottom wrote on Mon, 08 June 2009 10:47
I'd have gone with MAX() - MIN() but that still hits the same problem.

This is a strong contender for the 'Most arbitrary function' award.

LOL Simplicity just wooshed over my head.
Was so fixed on the "it has to be possible using AVG" route that I missed the obvious.

And here I was, staring proudly at my solution, thinking "hey, and I ain't even a mathemagician!"

Smile
Re: find difference of two numbers in oracle sql [message #407031 is a reply to message #407027] Mon, 08 June 2009 04:00 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Elegant. You need an ABS to ensure the value is positive, but that's a nice alernative solution.
Re: find difference of two numbers in oracle sql [message #407033 is a reply to message #407031] Mon, 08 June 2009 04:07 Go to previous messageGo to next message
karthick_arp
Messages: 13
Registered: February 2006
Location: hyderabad
Junior Member
order by c2 desc


This piece of code takes care of that. rno=1 will always be the greater value. So i thing we don't need ABS
Re: find difference of two numbers in oracle sql [message #407035 is a reply to message #407033] Mon, 08 June 2009 04:11 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I had just spotted that and was about to amend my post.
Re: find difference of two numbers in oracle sql [message #407105 is a reply to message #406993] Mon, 08 June 2009 08:21 Go to previous message
rtnataraj
Messages: 102
Registered: December 2008
Senior Member

Great Karthick_arp,

This solved my purpose.

Thank You very much & All.

regards,
Nataraj

Previous Topic: Index not used
Next Topic: dbms_sql.parse or execute immediate gives buffer too small error
Goto Forum:
  


Current Time: Mon Dec 05 02:38:22 CST 2016

Total time taken to generate the page: 0.11593 seconds