总数超过1的总和

目前我执行以下代码

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 ''' enter image description here

我想插入一个差异字段,该字段的计算方式为[(系统a的namount)-(系统z的namount)]。系统z的namount的计算方式为(购买的总namount-出售的总namount)。

请帮助我获得一个sql以实现这一点,非常感谢,我衷心感谢您的帮助。

评论
  • Liz
    Liz 回复
    计算为[(系统a的数量)-(系统z的数量)]

    如果这是您想要的,则可以使用窗口功能:

    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