How to group by diapasons?

Question

I have got a table 'payments'

user_id amount
1       300
1       100
2       100
2       100
3       10
4       200

What query should I make to display result by groups:

diapason       number
0 -10          0
10 - 100       1
100 -200       3
more than 200  2

I think I should use 'having'?

You gotta learn how to search before posting. There are lots of answers on SO to this problem. — P.Salmon, Sep 04 '18 at 08:41
@V.G. the issue with the proposed duplicate is that it doesn't return ranges where there are no values — Nick, Sep 04 '18 at 08:48

score 1 · Accepted Answer · answered Sep 04 '18 at 08:42

For this type of query you could use a UNION:

SELECT '0-10' as diapason, SUM(CASE WHEN amount < 10 THEN 1 ELSE 0 END) AS number FROM payments
UNION
SELECT '10-100', SUM(CASE WHEN amount BETWEEN 10 AND 99 THEN 1 ELSE 0 END) FROM payments
UNION
SELECT '100-200', SUM(CASE WHEN amount BETWEEN 100 AND 199 THEN 1 ELSE 0 END) FROM payments
UNION
SELECT 'more than 200', SUM(CASE WHEN amount >= 200 THEN 1 ELSE 0 END) FROM payments

Output:

diapason        number
0-10            0
10-100          1
100-200         3
more than 200   2

SQLFiddle Demo

How to group by diapasons?

1 Answers1