Django select count of users acquired points in range

Question

I have table like that:

user_id | points
--------+--------
1         10
1         500
2         2
2         6
3         5
3         50

Now I want to calculate how many users has SUM(points) between, for example, between 1 and 80, betwee 81 and 140, etc. How can I get it? In SQL it looks like it can be done only vie embedded SQL query - internal for grouping with HAVING clause and external - for counting rows in resultset.

@BurhanKhalid sorry, looks lie I missing something. What is slot/bucket? I it is about performance, tale may contains about 1kk records, not more. — Alex G.P., Sep 09 '14 at 06:31
Grouping by a `CASE ... WHEN ...` does the trick in these cases. THere's no nice way to do it with the Django ORM directly — Wolph, Sep 09 '14 at 07:08

score 0 · Answer 1 · answered Sep 09 '14 at 08:31

Got it.

#Declaring grouped data ranges (`lte` is `<=`, `gte` is `>=`, no postfix is `=`)
points_grouping_data = {
    'A22': { 'cc__lte': 50 },
    'B22': { 'cc__gte': 51, 'cc__lte': 150 },
    'C22': { 'cc__gte': 151, 'cc__lte': 199 },
    'D22': { 'cc': 200 },
    'E22': { 'cc__gte': 201, 'cc__lte': 300 },
    'F22': { 'cc__gte': 300, 'cc__lte': 400 },
    'G22': { 'cc__gte': 400, 'cc__lte': 499 },
    'H22': { 'cc': 500 },
    'I22': { 'cc__gt': 500 },
}

for cell, params in points_grouping_data.items():
    value = PointsLog.objects.values('user').annotate(
        cc=Sum('points')
    ).filter(**params).count()

Django select count of users acquired points in range

1 Answers1