I would like to count the aggregate function, for eg:
SELECT customer_id, SUM(amount)
FROM payment
GROUP BY customer_id
HAVING SUM(amount)>100;
So, how do I use COUNT() on SUM() to count the filtered SUM()?
2 Answers
You wrap it in an outer query.
select count(*) from (
SELECT customer_id, SUM(amount)
FROM payment
GROUP BY customer_id
HAVING SUM(amount)>100
) big_spenders
Comments
If you want to count the number of customers for each amount that you get from your query, you need a 2nd level of aggregation:
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;
Or, with COUNT() window function:
SELECT DISTINCT SUM(amount) amount,
COUNT(*) OVER (PARTITION BY SUM(amount)) counter
FROM payment
GROUP BY customer_id
HAVING SUM(amount)>100;
See a simplified demo.
1 Comment
B.LeeN
Never thought of using 2nd level of aggregation. Thank you for answering and demo!
HAVINGclause is already doing this. Add sample data to your question for best results.