MySQL 統計某欄位各值出現次數：GROUP BY + COUNT(1) 單條 SQL 秒出結果

MySQL 統計某欄位各值出現次數：GROUP BY + COUNT(1) 單條 SQL 秒出結果

需求：確認 material 表 type 欄位中 news、image 等各值各有多少筆，且未來可能新增其他值，需通用統計。

資料樣貌

核心 SQL

SELECT type, COUNT(1) AS counts
FROM material
GROUP BY type;

結果範例

type   | counts
-------|-------
news   | 1250
image  | 890
video  | 320
...    | ...

關鍵提示

• COUNT(1) vs COUNT(*)：效能相同 (InnoDB 優化器會自動轉換)，但 COUNT(1) 語義更明確「計算行數」，習慣上推薦。
• 若需同時顯示佔比：

SELECT
  type,
  COUNT(1) AS counts,
  ROUND(COUNT(1) * 100.0 / SUM(COUNT(1)) OVER (), 2) AS pct
FROM material
GROUP BY type;

（需 MySQL 8.0+ 窗口函數支援）

擴展：前 N 大分類

SELECT type, COUNT(1) AS counts
FROM material
GROUP BY type
ORDER BY counts DESC
LIMIT 10;

一條 SQL 解決動態分類統計，無需應用層迴圈查詢，效能與簡潔度雙贏。