請教各位大神若需要將每月1-31號業績加總
我這段code要加在哪裡?
且店櫃不能重複
SELECT
dh.shop_code 店編,
sp.shop_cname 店名,
dh.member_code 會員編號,
ms.chinese_name,
to_char(dh.inv_date, 'YYYY/MM/DD') 消費日期,
dh.inv_totalamt 總金額
FROM
dinv_hdr dh
INNER JOIN membership ms ON ( dh.member_code = ms.member_code )
INNER JOIN shop sp ON ( sp.shop_code = dh.shop_code )
WHERE
dh.inv_totalamt >= 2000
AND to_char(dh.inv_date, 'yyyy-mm-dd') >= '2022-05-01'
AND to_char(dh.inv_date, 'yyyy-mm-dd') <= '2022-05-31'
--AND dh.voided = '0'
--AND dh.closed = '1'
ORDER BY
店編,
消費日期
你測試一下
SELECT
dh.shop_code 店編,
sp.shop_cname 店名,
--dh.member_code 會員編號,
--ms.chinese_name,
--to_char(dh.inv_date, 'YYYY/MM/DD') 消費日期,
SUM(dh.inv_totalamt) 總金額
FROM
dinv_hdr dh
INNER JOIN membership ms ON ( dh.member_code = ms.member_code )
INNER JOIN shop sp ON ( sp.shop_code = dh.shop_code )
WHERE
dh.inv_totalamt >= 2000
AND to_char(dh.inv_date, 'yyyy-mm-dd') >= '2022-05-01'
AND to_char(dh.inv_date, 'yyyy-mm-dd') <= '2022-05-31'
--AND dh.voided = '0'
--AND dh.closed = '1'
GROUP BY --(Group by的欄位要跟Select對應,除了使用聚合函數的欄位都應該要在以下)
dh.shop_code,sp.shop_cname --(此處不能使用別名,請用欄位名)
ORDER BY
dh.shop_code