sum on having count more than 1

Currently I execute below code

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 get below data ”’

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.

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