詢問 CASE WHEN 用身分證區分個、法金GROUP BY 加總

sql

mike51620 2022-11-03 14:30:24 ‧ 844 瀏覽

分享至

我在撰寫 SQL CASE WHEN 時，想判段 ID 欄位為個、法金進行 GROUP BY 加總

    SELECT SUBSTRING(TXNT_DATE,1,6) AS TXNT_DATE,
	CASE WHEN SUBSTRING(CUSTID,9,1) = '' THEN '法金' ELSE '個金' END AS CLIENT_CODE,
	CASE WHEN SUBSTRING(CUSTID,9,1) = '' THEN  COUNT(0)
	ELSE COUNT(0) END AS 總筆數,
	CASE WHEN SUBSTRING(CUSTID,9,1) = '' THEN  SUM(EXCH_SPREAD_AMT)
	ELSE SUM(EXCH_SPREAD_AMT) END AS 匯差分配

	FROM MIEXRT A
	JOIN WPSRPM WPSRPM ON WPSRPM.MESG_OUT = 'O'
	WHERE  AFFAIR_SUB_CODE ='EB' AND  SUBSTRING(TXNT_DATE,1,6) IN ('202209')
	GROUP BY SUBSTRING(TXNT_DATE,1,6),SUBSTRING(CUSTID,9,1)
	ORDER BY TXNT_DATE
    
    
![https://ithelp.ithome.com.tw/upload/images/20221103/20137200kYstSINERE.png](https://ithelp.ithome.com.tw/upload/images/20221103/20137200kYstSINERE.png)

法金加總是沒什麼問題，但個金就是無法加總成一行
不知道用 CASE WHEN 的方式有沒辦法達成 = =
呈現的感覺如下，懇請大佬支援

![https://ithelp.ithome.com.tw/upload/images/20221103/201372004h0ECJXuKW.png](https://ithelp.ithome.com.tw/upload/images/20221103/201372004h0ECJXuKW.png)

rogeryao iT邦超人 7 級 ‧ 2022-11-03 14:45:31 檢舉

請建立模擬資料 https://dbfiddle.uk/-SXv3GHO

尼克 iT邦大師 1 級 ‧ 2022-11-03 14:46:05 檢舉

看不懂你要表達意思及問題？

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

1 個回答

johncoc

iT邦新手 3 級 ‧ 2022-11-03 14:58:08

最佳解答

會出現多種個金是因為 SUBSTRING(CUSTID,9,1) <> '' 有多種結果,所以要對最終結果做分組

SELECT SUBSTRING(TXNT_DATE,1,6) AS TXNT_DATE,
	CASE WHEN SUBSTRING(CUSTID,9,1) = '' THEN '法金' ELSE '個金' END AS CLIENT_CODE,
	CASE WHEN SUBSTRING(CUSTID,9,1) = '' THEN  COUNT(0)
	ELSE COUNT(0) END AS 總筆數,
	CASE WHEN SUBSTRING(CUSTID,9,1) = '' THEN  SUM(EXCH_SPREAD_AMT)
	ELSE SUM(EXCH_SPREAD_AMT) END AS 匯差分配

	FROM MIEXRT A
	JOIN WPSRPM WPSRPM ON WPSRPM.MESG_OUT = 'O'
	WHERE  AFFAIR_SUB_CODE ='EB' AND  SUBSTRING(TXNT_DATE,1,6) IN ('202209')
	GROUP BY SUBSTRING(TXNT_DATE,1,6),SUBSTRING(CUSTID,9,1)
	ORDER BY TXNT_DATE

改成

SELECT SUBSTRING(TXNT_DATE,1,6) AS TXNT_DATE,
	CASE WHEN SUBSTRING(CUSTID,9,1) = '' THEN '法金' ELSE '個金' END AS CLIENT_CODE,
	COUNT(0) AS 總筆數,
	SUM(EXCH_SPREAD_AMT) AS 匯差分配
	FROM MIEXRT A
	JOIN WPSRPM WPSRPM ON WPSRPM.MESG_OUT = 'O'
	WHERE  AFFAIR_SUB_CODE ='EB' AND  SUBSTRING(TXNT_DATE,1,6) IN ('202209')
	GROUP BY SUBSTRING(TXNT_DATE,1,6),CASE WHEN SUBSTRING(CUSTID,9,1) = '' THEN '法金' ELSE '個金' END 
	ORDER BY TXNT_DATE