如何對特定列和 COUNT() 其他列進行 GROUP BY？

我查詢了幾個表，結果如下：

我想在 ct_id(course) col 上計算正確的 col。我查詢了很多，但沒有成功。如何查詢這樣的事情？

例如，在 SELECT 中，我應該有兩列：-correctNo -incorrectNo for each course(ct_id)。

注意：最後一列（正確）是派生列：IF(selected_answer=answer,1,0) 為正確

就像是：

select ct_id, sum(correct) as num_correct, sum(1-correct) as num_incorrect
from your_view group by ct_id;

可能是您正在尋找的那種東西。

我不太確定您的確切要求是什麼，但我認為您正在尋找這個..

CREATE TABLE test1(User_ID SMALLINT, Jqci_ID SMALLINT, Ct_ID SMALLINT, Correct BIT)

INSERT INTO test1
SELECT 29, 5, 8, 0 UNION ALL
SELECT 29, 25, 8, 1 UNION ALL
SELECT 29, 26, 8, 0 UNION ALL
SELECT 29, 27, 8, 0 UNION ALL
SELECT 29, 28, 8, 0 UNION ALL
SELECT 29, 34, 8, 1 UNION ALL
SELECT 29, 35, 8, 0 UNION ALL
SELECT 29, 36, 8, 0 UNION ALL
SELECT 29, 37, 8, 0 UNION ALL
SELECT 29, 38, 8, 0 UNION ALL
SELECT 29, 39, 8, 0 UNION ALL
SELECT 29, 41, 8, 1 UNION ALL
SELECT 29, 42, 8, 0 UNION ALL
SELECT 29, 47, 34, 0


SELECT
   Ct_ID,
   SUM(CASE WHEN Correct = 1 THEN 1 ELSE 0 END) AS Correct,
   SUM(CASE WHEN Correct = 0 THEN 1 ELSE 0 END) AS InCorrect
FROM test1
GROUP BY Ct_ID

引用自：https://dba.stackexchange.com/questions/15840