SQL COUNT 裡面加入 WHERE 過濾

最近在開發 Laravel 專案需要做些統計，eloquent 不太熟悉且效率很差，因此講求效率的查詢仍使用 Raw SQL

前情提要

• 需要過濾出所有使用者的答對數量、答題總數

期待輸出：

UserID	CORRECT	TOTAL
1	27	45
2	18	61

嘗試過的方式

(SELECT BH.UserID as UID, COUNT(AH.choice = Q.ans) AS correct 
 FROM ans_histories AH, battle_histories BH, questions Q 
 WHERE BH.BHID = AH.battleID AND AH.questionID = Q.QID AND AH.choice = Q.ans 
 GROUP BY BH.UserID)

UNION

(SELECT BH.UserID, COUNT(*) 
 FROM ans_histories AH, battle_histories BH, questions Q 
 WHERE BH.BHID = AH.battleID AND AH.questionID = Q.QID AND AH.choice IS NOT NULL 
 GROUP BY BH.UserID)

行不通原因：下面那個會被當作獨立的，不會以相同 UserID 跟上面的敘述結合

UserID	CORRECT
1	27
2	18
1	45
2	61

解法

SELECT UserID, COUNT(CASE AH.choice WHEN Q.ans THEN 1 ELSE NULL END) AS correct, COUNT(*) 
FROM ans_histories AH, battle_histories BH, questions Q 
WHERE BH.BHID = AH.battleID AND AH.questionID = Q.QID AND AH.choice IS NOT NULL 
GROUP BY BH.UserID;

其中

COUNT(CASE AH.choice WHEN Q.ans THEN 1 ELSE NULL END)

可以替換成

SUM(CASE AH.choice WHEN Q.ans THEN 1 ELSE 0 END)

參考資料

https://stackoverflow.com/questions/1400078/is-it-possible-to-specify-condition-in-count