SQL 統計欄位非 NULL 筆數：子查詢加總

需求

表格欄位 studentLoginId、parentLoginId，分別統計非 NULL 筆數並求和。

單查詢完成

SELECT
    SUM(CASE WHEN studentLoginId IS NOT NULL THEN 1 ELSE 0 END) AS student_cnt,
    SUM(CASE WHEN parentLoginId IS NOT NULL THEN 1 ELSE 0 END) AS parent_cnt,
    SUM(CASE WHEN studentLoginId IS NOT NULL THEN 1 ELSE 0 END) +
    SUM(CASE WHEN parentLoginId IS NOT NULL THEN 1 ELSE 0 END) AS total_cnt
FROM your_table;

子查詢寫法（原回答）

SELECT
    (SELECT COUNT(*) FROM your_table WHERE studentLoginId IS NOT NULL) AS student_cnt,
    (SELECT COUNT(*) FROM your_table WHERE parentLoginId IS NOT NULL) AS parent_cnt,
    (SELECT COUNT(*) FROM your_table WHERE studentLoginId IS NOT NULL) +
    (SELECT COUNT(*) FROM your_table WHERE parentLoginId IS NOT NULL) AS total_cnt;

效能比較

• 單掃描 CASE WHEN 只需全表掃描一次，效能較佳。
• 子查詢掃描三次，適合小表或有適當索引時。

索引建議

CREATE INDEX idx_student_login ON your_table(studentLoginId) WHERE studentLoginId IS NOT NULL;
CREATE INDEX idx_parent_login ON your_table(parentLoginId) WHERE parentLoginId IS NOT NULL; -- 部分索引 (PostgreSQL/SQL Server 2019+)