| SQL- Help [message #371699] |
Mon, 27 November 2000 14:33  |
Srini
Messages: 28
Registered: November 2000
|
Junior Member |
|
|
Can anybody let me know how to find the difference of the values of a column in a single sql statement.
tablea:
ssn meet# time(secs)
--------------------
xyz 1 100
xyz 2 200
Now I need to find out the differnce between time for meet#1 and meet#2 in a sql query. Any help is appreciated.
|
|
|
|
| Re: SQL- Help [message #371710 is a reply to message #371699] |
Tue, 28 November 2000 01:52  |
J.S HAN
Messages: 12
Registered: November 2000
|
Junior Member |
|
|
Hi,
simply
select ssn,
min(decode(meet#,2,times)) - min(decode(meet#,1,times)) diff
from tablea
group by ssn
if this case
SSN MEET# TIMES
----- --------- ---------
xyz 1 100
xyz 2 200
abc 1 100
abc 2 300
xyz 3 350
select ssn, meet#, times, times - st
from ( SELECT
x.ssn ssn ,
MIN(DECODE(y.no,1,x.times)) st,
MIN(DECODE(y.no,2,x.times)) times,
MIN(decode(y.no,2,x.meet#)) meet#
FROM(SELECT ssn, meet#, times
FROM tablea
GROUP BY ssn, meet#, times) x,
(select 1 no from dual union all select 2 no from dual) y
WHERE y.no <= 2
GROUP BY
x.ssn, DECODE(y.no, 1, x.meet#, x.meet# - 1))
where meet# > 0
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
