how to get result from subtracting select statement

maybe my word will be confusing for you all..actually i want to deduct
2 select statement to get a result.this is due to not knowing how to
use coding in mssql...i'm using visual studio 2005...this is the select
statement...
SELECT a.no_akaun, a.nama_penyewa, SUM(b.Amaun) AS sum1, ' ' AS
sum2
FROM spr_penyewa a,SPR_Lejer b
WHERE (b.ID_Hasil = '76159' AND b.Amaun > 0)-(b.ID_Hasil = '76159')
AND (b.Amaun < 0)
and a.no_akaun=b.no_akaun
GROUP BY a.No_Akaun, a.nama_penyewa
SELECT a.no_akaun, a.nama_penyewa, ' ' AS sum1, SUM(b.Amaun*-1) AS
sum2
FROM spr_penyewa a, SPR_Lejer b
WHERE (b.ID_Hasil = '76159') AND (b.Amaun < 0)
and a.no_akaun=b.no_akaun
GROUP BY a.No_Akaun, a.nama_penyewa
how can i deduct those to...let say
<<<if
sum1-sum2>0 then
sum3
else
sum4......>>Select case sum1-sum2>0,sum3,sum4 end as Diff ,Sum1,sum2 from
(SELECT a.no_akaun, a.nama_penyewa, SUM(b.Amaun) AS sum1, 0 as sum2
AS
> sum2
> FROM spr_penyewa a,SPR_Lejer b
> WHERE (b.ID_Hasil = '76159' AND b.Amaun > 0)-(b.ID_Hasil = '76159')
> AND (b.Amaun < 0)
> and a.no_akaun=b.no_akaun
> GROUP BY a.No_Akaun, a.nama_penyewa
Union
SELECT a.no_akaun, a.nama_penyewa, 0 AS sum1, SUM(b.Amaun*-1) AS
> sum2
> FROM spr_penyewa a, SPR_Lejer b
> WHERE (b.ID_Hasil = '76159') AND (b.Amaun < 0)
> and a.no_akaun=b.no_akaun
> GROUP BY a.No_Akaun, a.nama_penyewa)
wiraperkasa wrote:
> maybe my word will be confusing for you all..actually i want to deduct
> 2 select statement to get a result.this is due to not knowing how to
> use coding in mssql...i'm using visual studio 2005...this is the select
> statement...
> SELECT a.no_akaun, a.nama_penyewa, SUM(b.Amaun) AS sum1, ' ' AS
> sum2
> FROM spr_penyewa a,SPR_Lejer b
> WHERE (b.ID_Hasil = '76159' AND b.Amaun > 0)-(b.ID_Hasil = '76159')
> AND (b.Amaun < 0)
> and a.no_akaun=b.no_akaun
> GROUP BY a.No_Akaun, a.nama_penyewa
> SELECT a.no_akaun, a.nama_penyewa, ' ' AS sum1, SUM(b.Amaun*-1) AS
> sum2
> FROM spr_penyewa a, SPR_Lejer b
> WHERE (b.ID_Hasil = '76159') AND (b.Amaun < 0)
> and a.no_akaun=b.no_akaun
> GROUP BY a.No_Akaun, a.nama_penyewa
> how can i deduct those to...let say
> <<<if
> sum1-sum2>0 then
> sum3
> else
> sum4......>>|||Sorry,Some typos in my Previous mail.Please correct that by this query
Select case
when sum1-sum2>0 then sum3
Else sum4
end as Diff ,Sum1,sum2 from
(SELECT a.no_akaun, a.nama_penyewa, SUM(b.Amaun) AS sum1, 0 as sum2
AS
sum2
FROM spr_penyewa a,SPR_Lejer b
WHERE (b.ID_Hasil = '76159' AND b.Amaun > 0)-(b.ID_Hasil ='76159')
AND (b.Amaun < 0)
and a.no_akaun=b.no_akaun
GROUP BY a.No_Akaun, a.nama_penyewa
Union
SELECT a.no_akaun, a.nama_penyewa, 0 AS sum1, SUM(b.Amaun*-1) AS
sum2
FROM spr_penyewa a, SPR_Lejer b
WHERE (b.ID_Hasil = '76159') AND (b.Amaun < 0)
and a.no_akaun=b.no_akaun
GROUP BY a.No_Akaun, a.nama_penyewa)|||i have rewrite the code but still i got and error..this is the select
statement..
Select case
when sum1-sum2>0 then sum3
Else sum4
end as Diff ,Sum1,sum2 from
(SELECT a.no_akaun, a.nama_penyewa, SUM(b.Amaun) AS sum1, 0 as sum2
FROM spr_penyewa a,SPR_Lejer b
WHERE b.ID_Hasil = '76159' AND b.Amaun > 0
and a.no_akaun=b.no_akaun
GROUP BY a.No_Akaun, a.nama_penyewa
Union
SELECT a.no_akaun, a.nama_penyewa, 0 AS sum1, SUM(b.Amaun*-1) AS
sum2
FROM spr_penyewa a, SPR_Lejer b
WHERE b.ID_Hasil = '76159' AND b.Amaun < 0
and a.no_akaun=b.no_akaun
GROUP BY a.No_Akaun, a.nama_penyewa)
the error said that "incorrect syntax near ')'..."could somebody help
me again with this:)|||Forgot to add Alias
Select case
when A.sum1-A.sum2>0 then A.sum3
Else A.sum4
end as Diff ,A.Sum1,A.sum2 from
(SELECT a.no_akaun, a.nama_penyewa, SUM(b.Amaun) AS sum1, 0 as
sum2
FROM spr_penyewa a,SPR_Lejer b
WHERE b.ID_Hasil = '76159' AND b.Amaun > 0
and a.no_akaun=b.no_akaun
GROUP BY a.No_Akaun, a.nama_penyewa
Union
SELECT a.no_akaun, a.nama_penyewa, 0 AS sum1, SUM(b.Amaun*-1) AS
sum2
FROM spr_penyewa a, SPR_Lejer b
WHERE b.ID_Hasil = '76159' AND b.Amaun < 0
and a.no_akaun=b.no_akaun
GROUP BY a.No_Akaun, a.nama_penyewa) A|||thank you rajdeep...but now its say "invalid column name 'sum3' and
'sum4'..."|||I mean sum3 and sum4 are the column names which you want to
show.replace with appropriate column names as you wish
I just mentioned them for your reference
wiraperkasa wrote:
> thank you rajdeep...but now its say "invalid column name 'sum3' and
> 'sum4'..."|||i change it to this..
Select case
when A.sum1-A.sum2>0 then A.sum3
Else A.sum4
end as Diff ,A.Sum1,A.sum2 from
(SELECT a.no_akaun, a.nama_penyewa, SUM(b.Amaun) AS sum1, 0 as
sum2, 0 as sum3, 0 as sum4
FROM spr_penyewa a,SPR_Lejer b
WHERE b.ID_Hasil = '76159' AND b.Amaun > 0
and a.no_akaun=b.no_akaun
GROUP BY a.No_Akaun, a.nama_penyewa
Union
SELECT a.no_akaun, a.nama_penyewa, 0 AS sum1, SUM(b.Amaun*-1) AS
sum2, 0 as sum3, 0 as sum4
FROM spr_penyewa a, SPR_Lejer b
WHERE b.ID_Hasil = '76159' AND b.Amaun < 0
and a.no_akaun=b.no_akaun
GROUP BY a.No_Akaun, a.nama_penyewa) A
and at the end its only show sum1 and sum2...i still lost..plz help
me..rajdeep..what do u mean by the last post?replace it with the column
from the table?i'm confuse..coz sum3 and sum4 should be another
field that are created from the "case" statement right..or am i
wrong...i'm totally lost:(