向查询添加SQL Sum

Question

我试图添加一个SQL Sum来显示IN_QTY的总数和加载的总数。示例:在截图中，我为IN_QTY加载了200个，基于LOGIN_DTTM，我加载了75个。

我想让它像这样显示200/75。

有人能帮忙处理这个请求吗?这是我第一次使用Stack overflow

我查询:

Select a.lot, a.lpt, a.opn, a.in_Qty,  a.device, a.arrival_Dttm, a.login_dttm, a.priority, a.location
From
(Select  lma.lot, lma.lpt, lma.opn, lma.device, lma.in_qty, arrival_dttm, login_dttm, lco.equip_grp, ls.priority, ls.lot_code3 as location
From lot_cur_opn lco, lot_move_age lma, dm_device_attributes dda, lot_str ls
where lco.facility = lma.facility
and dda.facility = lma.facility
and lma.facility = ls.facility
and dda.device = lma.device
and lco.lot = lma.lot
and lma.lot = ls.lot
and lma.facility = 'DP1DM5'
and lco.opn in ('4927')  --specify query operation
and lma.departure_dttm is null
and lma.latest = 'O'
and ls.latest = 'Y'
and dda.family not like '%PILOT%'
and dda.family not like '%NONE%'
and dda.family not like '%ENG%'
and dda.family not like '%LBQ%'
) a
Join
(Select equip_grp, substr(trk_id,0,3)
From equip_grp_trk_lst egl
where egl.stop_dttm is null
and egl.status = 'A'
and egl.trk_id like 'SE2%'  --specify equipment type
and egl.trk_id not like '%LOGTHR%'
group by equip_grp, substr(trk_id,0,3)) b
On b.equip_grp = a.equip_grp
order by priority, arrival_dttm

截图:电流输出

Answer 1

试试这样:

select sum(in_qty), 
sum(case when login_dttm is not null then in_qty end)
from 
(
... your big query ...
)

这将把所有的in_qty加起来，并且只包括那些login_dttm的值不为空的值作为单独的和。