为什么COUNT（*）返回多个结果在这个子查询（PostgreSQL的）？(Why is count

我想组COUNT（*）查询到价值桶的结果。我的dellstore2 PostgreSQL的示例数据库测试此。我下面的查询返回正确的答案，但在表（几千相同的结果），每行这样做一次。我可以通过添加解决这个问题LIMIT 1查询的结束，但我想知道为什么我得到的情况下，它指向与我的做法更广泛的问题重复。查询是：

SELECT
    (SELECT count(*)
        FROM
            orders
        WHERE
            totalamount > 0 AND totalamount <= 100) AS ">0 <= 100",
    (SELECT count(*)
        FROM
            orders
        WHERE
            totalamount > 100 AND totalamount <= 200) AS ">100 <= 200"
...
FROM
    orders;

编辑Andomar的回答也让我找到下面的方法（改编自SQL简而言之（O'Reilly）的一个例子）。这让我有水桶一列，用一排每个桶/答案配对。我想我应该包括它与使用情况的人：

SELECT CASE
        WHEN totalamount IS NULL THEN 'Unknown'
        WHEN totalamount <= 100 THEN 'Not more than 100'
        WHEN totalamount <= 200 THEN 'Not more than 200'
        ELSE 'Over 200'
    END "Bucket",
    COUNT(*) "Number of results"
FROM
    orders
GROUP BY CASE
        WHEN totalamount IS NULL THEN 'Unknown'
        WHEN totalamount <= 100 THEN 'Not more than 100'
        WHEN totalamount <= 200 THEN 'Not more than 200'
        ELSE 'Over 200'
    END
ORDER BY
    MIN(totalamount);

Answer 1:

你从选择的每一行orders ，然后对每一行，子查询进行评估。

考虑这种方法代替：

select  count(case when 0 < totalamount and totalamount <= 100 then 1 end)
            as "<0,100]"
,       count(case when 100 < totalamount and totalamount <= 200 then 1 end)
            as "<100,200]"
from    Orders

这将在一个单一的表扫描计算都聚集。