Currently I execute below code
x
select system,aid,mid,buysell,sdate,medate,namount,type, count(*)
from databasefile
where mid in ('123456')
group by system,aid,mid,buysell,sdate,medate,namount,type
order by aid
I want to insert a difference field, which is calculated as [(namount for system a) – (namount for system z) ]. namount for system z is calculated as (total namount for buys – total namounts for sells).
please help me get a sql to achieve this, thanks very much and I sincerely appreciate your help.
Advertisement
Answer
calculated as [(namount for system a) – (namount for system z) ]
If this is what you want, you can use window functions:
select system, aid, mid, buysell, sdate, medate, namount, type, count(*),
(sum(case when system = 'a' then namount else 0 end) over () -
sum(case when system = 'z' then namount else 0 end) over ()
)
from databasefile
where mid in ('123456')
group by system, aid, mid, buysell, sdate, medate, namount, type
order by aid