django queryset for grouping and counting

Question

Details.objects.all()

Dept      Gender   Shift
Software  Male     Day
Software2 Female   Night

what i want is json like {"Gender":"Male", "count":2} using queryset. count is the number of male and female in specific dept.

im new to djnago and have tried this

Details.objects.values("Gender").annotate(count=Count("Gender")).order_by("Shift")

where i get

[{'count': 3, 'Gender': u'Female'}, {'count': 1, 'Gender': u'Male'}, {'count': 3, 'Gender': u'Female'}, {'count': 3, 'Gender': u'Male'}]

of both departments...I want one dept at a time(Software/Software2). Please help:) Thanks.

{"Gender":"Male", "count":2} this is my expected output. i need to get theie count and gender by dept — cloud
– cloud, Commented Jul 18, 2017 at 6:52
Have you tried Details.objects.values("Dept", "Gender", "Shift").annotate(count=Count("Gender")) — zaidfazil
– zaidfazil, Commented Jul 18, 2017 at 7:24
@FazilZaid thank you:) but can u help me make it more precise so i get {"Gender":"Male", "count":2} from software dept only. — cloud
– cloud, Commented Jul 18, 2017 at 7:35
Details.objects.filter(dept='Software').values("Dept", "Gender", "Shift").annotate(count=Count("Gender")) — Exprator
– Exprator, Commented Jul 18, 2017 at 7:38

MiniGunnR · Accepted Answer · 2017-07-18 07:54:22Z

1

The following query takes gender and department as input and gives out count as output in JSON.

day_male_count = Details.objects.filter(gender='Male', department='Software', shift='Day').count()

night_male_count = Details.objects.filter(gender='Male', department='Software', shift='Night').count()

I feel like this may not be what you were asking, is it?

edited Jul 18, 2017 at 7:54

answered Jul 18, 2017 at 6:36

MiniGunnR

5,8208 gold badges45 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

cloud Over a year ago

this gives a count of male in software dept. i want male count who works in software dept at day shift and night shift separetely.

MiniGunnR Over a year ago

@meow Changed the answer. Please check now.

mattjegan · Accepted Answer · 2017-07-18 07:20:46Z

The Django ORM makes this quite difficult to do using annotations and aggregates so I opted for a more pure-python approach.

from collections import Counter
d = list(Details.objects.values_list('Dept', 'Gender', 'Shift'))
response = [i + (c[i],) for i in Counter(d)]

What we are doing is pulling out the rows as tuples and then passing them into a python Counter which returns a dict-like object where the keys are the original tuples and the values are a count of the number of tuple occurrences.

This gives:

[
    ('software1', 'male', 'night', 2), 
    ('software1', 'female', 'day', 1), 
    ('software2', 'male', 'day', 1), 
    ('software2', 'female', 'day', 1), 
    ('software1', 'female', 'night', 1)
]

To dictify the result we run:

final = [{'Dept': i[0], 'Gender': i[1], 'Shift': i[2], 'Count': i[3]} for i in response]

Hope I helped.

Collectives™ on Stack Overflow

django queryset for grouping and counting

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related