TO FIND DIFFERENCE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

TO FIND DIFFERENCE

Author

Topic

HolyAngel
Starting Member

4 Posts

Posted - 2010-11-29 : 07:50:24

HI,

I have a table like,

NAME SPEND1 SPEND2
-----------------------------
XXX 50.10 -
XXX - 10.10
YYY 20 -
YYY - 5
ZZZ 35 -
ZZZ - 15

I NEED THE RESULT LIKE:

NAME SPEND1 SPEND2 DIFFERENCE
---------------------------------------
XXX 50.10 10.10 40
YYY 20 5 15
ZZZ 35 15 20

Appreciate, if someone helps me to achieve this..

Thanks,
Angel

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-29 : 07:54:18

select name, spend1 = max(spend1), spend2 = max(spend2), difference = max(spend1)-max(spend2)
from tbl
group by Name

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

HolyAngel
Starting Member

4 Posts

Posted - 2010-11-29 : 08:26:55

Thanks much..

Let me reframe my quest,

I have a table like,

NAME SPEND cond
-----------------------------
XXX 50.10 aaa
XXX 10.10 bbb
YYY 20 aaa
YYY 5 bbb
ZZZ 35 aaa
ZZZ 15 bbb

I have a condition for each name,
if Cond='aaa' then i need a field added to the resultset with Spend value as Cond1
if cond='bbb' then i need a field added to the resultset with Spend value as Cond2

NAME cond1 cond2 diff
----------------------------------
XXX 50.10 10.10 40
YYY 20 5 15
ZZZ 35 15 20

Thanks,
Angel

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-29 : 08:37:29

select name, cond1, cond2, diff = cond1-cond2
from (
select name, cond1 = max(case when cond = 'aaa' then spend else 0 end),
cond2 = max(case when cond = 'bbb' then spend else 0 end),
from tbl
group by Name
) a

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

HolyAngel
Starting Member

4 Posts

Posted - 2010-11-30 : 00:34:25

Thanks :-)

But i got this error..

ORA-00923: FROM keyword not found where expected
00923. 00000 - "FROM keyword not found where expected"
*Cause:

Thanks,
Angel

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-30 : 04:29:32

You need to convert to something that will run on oracle.
Maybe an oracle forum would help.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

HolyAngel
Starting Member

4 Posts

Posted - 2010-11-30 : 06:50:12

Thank You for ur help..

Yes.. I will post the query in Oracle Forum.

Thanks,
Angel

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources