Django: How to get count of a specific column according to two columns

Question

i want to get the count of the user id of this table according to the columns approval_transaction_type and approval_type.

the expected result of this would be.

Approval Transaction Type ID (60)

Approval Type ID (65) = 2 Users
Approval Type ID (64) = 2 Users
Approval Type ID (63) = 2 Users
Approval Type ID (62) = 2 Users
Approval Type ID (61) = 2 Users

My current code to achieve is this, but it overwrites the sub list and returns the incorrect result(Which i don't understand why the last array will overwrite all the array):

for transaction in transaction_types:

        # Initial Array 
        transaction["approval_types"] = []

        for approval_type in approval_types:

            # Get Count of Users
            approval_type["count"] = Model.objects.filter(approval_transaction_type=transaction['id'],approval_type=approval_type['id']).values().count()

            # Assign this sub list to main list
            transaction["approval_types"].append(approval_type)

How do i get the count without looping and use the queryset? Let me know if something is not clear about this. Thanks!

Django >1.8 has conditional aggregations. See https://docs.djangoproject.com/en/1.10/ref/models/conditional-expressions/ and you can use the case functionality. — wrdeman, Oct 13 '16 at 06:13
Possible duplicate of [Django equivalent for count and group by](http://stackoverflow.com/questions/327807/django-equivalent-for-count-and-group-by) — Sardorbek Imomaliev, Oct 13 '16 at 06:18

score 2 · Accepted Answer · edited May 23 '17 at 12:33

2

It can be done in one query. Based on this Django equivalent for count and group by

Model.objects.values('approval_type').annotate(Count('user'))

edited May 23 '17 at 12:33

Community

1
1

answered Oct 13 '16 at 06:18

Sardorbek Imomaliev

14,861
2
51
63

Django: How to get count of a specific column according to two columns

1 Answers1