Table EMP
DATE
EMPID
SCORE

我偏好不手動在 SQL 裡寫四則運算

WITH T AS(
SELECT EMPID, ISNULL(SCORE,'ETOTAL') ESCORE, COUNT('1') ECOUNT
FROM EMP
GROUP BY GROUPING SETS (EMPID,SCORE)
)
SELECT EMPID, ['A'],['B'],['ETOTAL'] 'A+B'
FROM T
WHERE EMPID IS NOT NULL
PIVOT
(
SUM(ECOUNT)
FOR ESCORE IN (['A'],['B'],['ETOTAL'])
) AS P
ORDER BY EMPID;

或者可以直接使用 PIVOT 然後把 A 與 B 加總

SELECT EMPID, ['A'],['B']
FROM T
PIVOT
(
COUNT(SCORE)
FOR SCORE IN (['A'],['B'])
) AS P
ORDER BY EMPID;

hardman0816提到：
請教大大們，TABLE 如下：
日期 員工 成績

張三 A
李四 B
張三 B
李四 B
張三 A
李四 A

要Show出如下結果，SQL如何下?
員工 成績A次數 成績B次數 A+B次數
=============...(恕刪)

SELECT a.員工,
(SELECT COUNT(b.*) FROM 員工成績 b WHERE b.員工＝a.員工 AND b.成績='A') AS 成績A次數,
(SELECT COUNT(b.*) FROM 員工成績 b WHERE b.員工＝a.員工 AND b.成績='A')
AS 成績B次數,
(SELECT COUNT(b.*) FROM 員工成績 b WHERE b.員工＝a.員工 AND b.成績 IN ('A','B'))
AS 成績A加B次數
FROM 員工資料 a

我們是 Oracle Database 技術顧問
我們是 ADempiere/Compiere 技術顧問
Skype: ADempiere/Compiere

MySQL 可以這樣寫：

<pre class="c" name="code">SELECT name, sum(if(grade='A',1,0)) as aa,sum(if(grade='B',1,0)) as bb,count(*) as ab FROM TABLE group by name

select 員工, sum(次數A) AS 次數A, sum(次數B) as 次數B, sum(次數A+次數B) AS "次數A+次數B" from(
select 員工, decode(成績, 'A', 1, NULL) AS 次數A
, decode(成績, 'B', 1, NULL) AS 次數B
from TABLE
) group by 員工