SELECT tr.id_kasir,
tr.tgl_transaksi,
CASE tr.id_member
WHEN 'M000' THEN Sum (mn.harga_menu * st.qty_menu) + Sum(
mn.harga_menu * st.qty_menu) * 0.1
- Nvl2 (pro.discount, Sum(mn.harga_menu * st.qty_menu)
*
( pro.discount / 100 ), 0
)
ELSE Sum(mn.harga_menu * st.qty_menu) -
Sum(mn.harga_menu * st.qty_menu) *
0.05 + Sum
(mn.harga_menu * st.qty_menu) * 0.1 -
Nvl2 (pro.discount, Sum(mn.harga_menu * st.qty_menu) * (
pro.discount / 100 ), 0
)
END Setoran
FROM transaksi tr
JOIN struk st
ON ( st.no_transaksi = tr.no_transaksi )
JOIN menu mn
ON ( st.kode_menu = mn.kode_menu )
LEFT OUTER JOIN promo pro
ON ( pro.kode_menu = mn.kode_menu )
GROUP BY tr.id_kasir,
tr.tgl_transaksi,
tr.id_member,
pro.discount
ORDER BY tr.tgl_transaksi,
tr.id_kasir
此查询正在运行,并且输出如下所示:
ID_KASIR TGL_TRANSAKSI SETORAN
C001 23-OCT-14 38000
C002 23-OCT-14 30800
C001 24-OCT-14 168000
C002 24-OCT-14 16500
C002 25-OCT-14 27500
C002 25-OCT-14 26250
但是我想总结一下setoran,分组依据id_kasir
,tgl_transaksi
有人可以给我解决方案吗?
您可以查询cte
并使用此结果进行分组。
with cte
as
(
your current query
)
select ID_KASIR , TGL_TRANSAKSI, SUM(SETORAN) as total
FROM cte
GROUP BY ID_KASIR, TGL_TRANSAKSI
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句