CODEWITHSUNDEEP
djangogroup-bydjango-queryset
MHB
MHB

Reputation: 695

groupby action on django queryset

I have a db table like this:

id|type_id | col1 | col2 | col3
0 |     1  |   2  |  b   |   c
1 |     2  |   1  |  a   |   a
2 |     2  |   2  |  a   |   c
3 |     1  |   3  |  b   |   c

I need to write a queryset to have:

[{'id':1, 'count_col1': 5, 'type_id__name':balh, 'col2':b},{'id':2, 'count_col1':3, 'type_id__name': balab, 'col2':a}]

how can I do it? it can be done by groupby in pandas, but I don't know how to do it here.

Upvotes: 1

Views: 56

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476614

It looks like you aim to calculate the sum of the col1s for each id. We can do that with:

from django.db.models import F, Sum

Model.objects.values('type_id').annotate(
    type_name=F('type__name'),
    count_col1=Sum('col1')
).order_by('type_id')

This will construct a QuerySet of dictionaries, that will look like:

<QuerySet [
    {'type_id': 1, 'type_name': 'foo', 'count_col1': 5},
    {'type_id': 2, 'type_name': 'bar', 'count_col1': 3}
]>

or if you want to include col2:

from django.db.models import F, Sum

Model.objects.values('type_id', 'col2').annotate(
    type_name=F('type__name'),
    count_col1=Sum('col1')
).order_by('type_id', 'col2')

Upvotes: 1

Related Questions