透视任务就是这种情况。
在您的特定情况下，CASE表达式可帮助您收集要求和的特定事件计数，然后您可以对year_values进行聚合。您可以使用LIKE运算符检测D值。

SELECT year_, 
       SUM(events_)                                              AS events_total,
       SUM(CASE WHEN category    = 'A'  THEN events_ ELSE 0 END) AS categoryA,
       SUM(CASE WHEN category    = 'B'  THEN events_ ELSE 0 END) AS categoryB,
       SUM(CASE WHEN category    = 'C'  THEN events_ ELSE 0 END) AS categoryC,
       SUM(CASE WHEN category LIKE 'D%' THEN events_ ELSE 0 END) AS categoryD
FROM tab
GROUP BY year_

检查here演示。
结合使用FILTER运算符和COALESCE函数，结果如下：

SELECT year_, 
       SUM(events_)                                               AS events_total,
       COALESCE(SUM(events_) FILTER(WHERE category    = 'A' ), 0) AS catA,
       COALESCE(SUM(events_) FILTER(WHERE category    = 'B' ), 0) AS catB,
       COALESCE(SUM(events_) FILTER(WHERE category    = 'C' ), 0) AS catC,
       COALESCE(SUM(events_) FILTER(WHERE category LIKE 'D%'), 0) AS catD
FROM tab
GROUP BY year_

查看here演示。