I have tables like below.
I would like to
counting by referreing to its score.
customer score A 10 A 20 B 30 B 40 C 50 C 60
First, I would like to sum
It achived by
group by method.
customer score A 30 B 70 C 110
second, I would like to
count by binning following band.
I couldn’t figure out how to count after grouping
band count 0-50 1 50-100 1 100- 0
Are there any way to achieve this?
You could use a union approach:
WITH cte AS ( SELECT COUNT(*) AS score FROM yourTable GROUP BY customer ) SELECT '0-50' AS band, COUNT(*) AS count, 0 AS position FROM cte WHERE score <= 50 UNION ALL SELECT '50-100', COUNT(*), 1 FROM cte WHERE score > 50 AND score <= 100 UNION ALL SELECT '100-', COUNT(*), 2 FROM cte WHERE score > 100 ORDER BY position;