Count and sum having amount range

Question

I have a table purchases containing user_id and items_amount.

Each time customer buys something, there is new record with his id and amount of items.

Now I need to see how many people have 1000+ purchased items, how many people have 1000-900 purchased items and so on.

I am now kind of lost with grouping, counting and summing this.

It actually counts users but shows me some (random?) amount as sum, so I dont fully understand how many people bought what. Is there any way to do it better? Is my query wrong? This makes me confused

SELECT count(*), sum(items_amount) as amount 

FROM `purchases`

WHERE 1

GROUP BY user_id

HAVING amount >= 1000 
   OR amount BETWEEN 1000 AND 900
   OR amount BETWEEN 900 AND 800
   OR amount BETWEEN 800 AND 700
   OR amount BETWEEN 700 AND 600
   OR amount BETWEEN 600 AND 500
   OR amount BETWEEN 500 AND 400
   OR amount BETWEEN 400 AND 300
   OR amount BETWEEN 300 AND 200
   OR amount BETWEEN 200 AND 100
   OR amount BETWEEN 100 AND 1

Output looks like this now but this amount is something i dont understand:

COUNT(*) AMOUNT

125      94969
4        2267
69       1352
148      2123
667      13695
433      1099
629      1482
697      1228
303      1633
415      1719

What I want to see is more or less:

count(*) amount

125      1000+
4        1000-900
69       900-800
148      800-700
667      700-600
433      600-500
....

I know it will not look exactly like this but I need to get amounts of people in these item ranges.

@BhargavChudasama `user_id` and `items_amount` are ints, contain only numbers — divHelper11, Oct 16 '18 at 11:36
Possible duplicate of [group by range in mysql](https://stackoverflow.com/questions/6687534/group-by-range-in-mysql) — P.Salmon, Oct 16 '18 at 11:39

score 1 · Answer 1 · answered Oct 16 '18 at 11:43

Without having any sample data to work from it's hard to be certain, but I think what you want is something like this. It takes your sum of items_amounts by user_id, and then groups it by ranges on amount:

SELECT
    CASE WHEN amount >= 1000 THEN '>=1000' 
         ELSE CONCAT('BETWEEN ', (amount DIV 100) * 100, ' AND ', (amount DIV 100) * 100 + 99)
    END AS amt,
    COUNT(p.user_id) AS num_users
FROM (SELECT SUM(items_amount) AS amount 
      FROM `purchases`
      WHERE 1
      GROUP BY user_id) p
GROUP BY amt

Count and sum having amount range

1 Answers1