pivot查询的问题在于COUNT函数将0计为1。所以改变：

COUNT(CASE WHEN extract('month' FROM date) = 1 THEN amount ELSE 0 END)

这是：

COUNT(CASE WHEN extract('month' FROM date) = 1 THEN amount END)

如果要使用if/else，请使用SUM()，如下所示：

SUM(CASE WHEN extract('month' FROM date) = 1 THEN amount ELSE 0 END)

但是请注意，从Postgres 9.4开始，FILTER子句得到支持。所以我会把你的完整查询写成这个版本：

SELECT
    institution_account,
    COUNT(amount) FILTER (WHERE extract('month' FROM date) = 1) AS Jan,
    COUNT(amount) FILTER (WHERE extract('month' FROM date) = 2) AS Feb,
    COUNT(amount) FILTER (WHERE extract('month' FROM date) = 3) AS Mar,
    COUNT(amount) FILTER (WHERE extract('month' FROM date) = 4) AS Apr,
    COUNT(amount) FILTER (WHERE extract('month' FROM date) = 5) AS May,
    COUNT(amount) FILTER (WHERE extract('month' FROM date) = 6) AS Jun,
    COUNT(amount) FILTER (WHERE extract('month' FROM date) = 7) AS Jul,
    COUNT(amount) FILTER (WHERE extract('month' FROM date) = 8) AS Aug,
    COUNT(amount) FILTER (WHERE extract('month' FROM date) = 9) AS Sep,
    COUNT(amount) FILTER (WHERE extract('month' FROM date) = 10) AS Oct,
    COUNT(amount) FILTER (WHERE extract('month' FROM date) = 11) AS Nov,
    COUNT(amount) FILTER (WHERE extract('month' FROM date) = 12) AS Dec
    COUNT(Amount) AS Total
FROM transactioning
GROUP BY institution_account

您在查询中遇到的问题与使用的查询函数有关。当您使用“0”时，它会计算行数，并且不会区分amount和0的值。这就是为什么所有月份都得到相同的结果，包括那些零计数的月份。要实现您想要的结果，您希望在正确处理零计数的同时计算每个月的行数：您可以将查询更新为

SELECT institution_account,
        SUM(CASE WHEN extract('month' FROM date) = 1 THEN 1 ELSE 0 END) AS Jan,
        SUM(CASE WHEN extract('month' FROM date) = 2 THEN 1 ELSE 0 END) AS Feb,
        SUM(CASE WHEN extract('month' FROM date) = 3 THEN 1 ELSE 0 END) AS Mar,
        SUM(CASE WHEN extract('month' FROM date) = 4 THEN 1 ELSE 0 END) AS Apr,
        SUM(CASE WHEN extract('month' FROM date) = 5 THEN 1 ELSE 0 END) AS May,
        SUM(CASE WHEN extract('month' FROM date) = 6 THEN 1 ELSE 0 END) AS Jun,
        SUM(CASE WHEN extract('month' FROM date) = 7 THEN 1 ELSE 0 END) AS Jul,
        SUM(CASE WHEN extract('month' FROM date) = 8 THEN 1 ELSE 0 END) AS Aug,
        SUM(CASE WHEN extract('month' FROM date) = 9 THEN 1 ELSE 0 END) AS Sep,
        SUM(CASE WHEN extract('month' FROM date) = 10 THEN 1 ELSE 0 END) AS Oct,
        SUM(CASE WHEN extract('month' FROM date) = 11 THEN 1 ELSE 0 END) AS Nov,
        SUM(CASE WHEN extract('month' FROM date) = 12 THEN 1 ELSE 0 END) AS Dec,
        COUNT(*) AS Total
   FROM transactioning
  GROUP BY institution_account;