postgresql - 同一张表中 COUNT 的 SUM

Question

我正在做很多我想在表格中显示的计数。我想在同一张表中显示所有计数的总和。

这是我得到的(简化 - 我有 6 个计数):

SELECT * FROM (SELECT COUNT() AS NB_book
        item as a1, metadatavalue as m1, metadatavalue as m12,
        WHERE m1.field_id = 64 (because I need that field to exist)
        AND m2.field_id = 66
        And m2. = book
        AND a1.in_archive = TRUE )
        (SELECT COUNT() AS NB_toys
        metadatavalue as m1, metadatavalue as m12,
         WHERE m1.field_id = 64 (because I need that field to exist)
        AND m2.field_id = 66
        And m2. = toys
         AND a1.in_archive = TRUE)
)

现在，我希望显示是这样的

------------表格------------
|NB_book | NB_玩具 |总对象 |
------------------------------
| 12 | 10 | 22 |

Answer 1

你想要的是:

SELECT
    sum(CASE WHEN condition_1 THEN 1 END) AS firstcount,
    sum(CASE WHEN condition_2 THEN 1 END) AS secondcount,
    sum(thecolumn) AS total
FROM ...

您的示例查询过于模糊，无法构建可用的内容，但这会给您思路。上面的条件可以是任何 bool 表达式。

如果您愿意，可以使用 NULLIF 而不是 CASE WHEN ... THEN ... END。我更喜欢坚持标准的 CASE。