How to sum and count in grouping in sql

I have tables like below. I would like to grouping and 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 score by customer 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?

Thanks

Answer

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;