如何在SQL中对另一个聚合函数使用COUNT函数？

Question

我想对聚合函数进行计数，例如：

SELECT customer_id, SUM(amount)
FROM payment
GROUP BY customer_id
HAVING SUM(amount)>100;

那么，如何在SUM()上使用COUNT()来计算过滤后的SUM()呢？

Answer 1

您将其包装在一个外部查询中。

select count(*) from (
  SELECT customer_id, SUM(amount)
  FROM payment
  GROUP BY customer_id
  HAVING SUM(amount)>100
) big_spenders

Answer 2

如果您想要计算从查询中获得的每个金额的客户数量，则需要第二级聚合：

SELECT amount, COUNT(*) counter
FROM (
  SELECT customer_id, SUM(amount) amount
  FROM payment
  GROUP BY customer_id
  HAVING SUM(amount)>100
) t
GROUP BY amount;

或者，使用COUNT()窗口函数：

SELECT DISTINCT SUM(amount) amount,
       COUNT(*) OVER (PARTITION BY SUM(amount)) counter
FROM payment
GROUP BY customer_id
HAVING SUM(amount)>100;

请参阅简化的demo。