分组依据- MS SQL Server

Question

我有一张像这样的桌子

Status  - Count
DELIVRD - 68926
Failed  - 6369
EXP-SMS-TMOUT   - 101
EXP-NW-TMOUT    - 97
EXP-MEM-EXCD    - 17
EXP-NW-FAIL - 109
EXP-HDST-BUSY   - 113
EXP-AbsSubs - 5732

我想要类似的结果

Delivered - 68926
Failed  -   6369
EXP - 6169   [Total(EXP-SMS-TMOUT,EXP-NW-TMOUT,EXP-MEM-EXCD,EXP-NW-FAIL,EXP-HDST-BUSY,EXP-AbsSubs)]

我们如何使用group by来实现这一点。请尽快分享查询。

Answer 1

SELECT 
  CASE WHEN Status LIKE 'EXP%' THEN 'EXP' ELSE Status END
, SUM([Count]) as [Count]
FROM Table1
GROUP BY CASE WHEN Status LIKE 'EXP%' THEN 'EXP' ELSE Status END

SQLFiddle DEMO