Django ORM for given group by SQL query with aggregation method sum and count

Question

I have below given Django model

class ABC(models.Model):
    user = models.ForeignKey(DEF)
    name = models.CharField()
    phone_num = models.CharField()
    date = models.DateTimeField(auto_now=True)
    amount = models.IntegerField()

I want to perform below query using Django ORM.

select *, sum(amount), count(date) from ABC group by phone_num;

I tried code below, but it does not work.

ABC.objects.all().annotate(count = Count("phone_num")).order_by("phone_num")

does your select work when you executing it directly in your sql studio ? — Andriy Ivaneyko, Jan 12 '16 at 13:44
Do you need that that `*`? I mean, what exactly fields from the query you're intending to use? — Alex Morozov, Jan 12 '16 at 13:54
yes, I need * in query, I am unable to product Django ORM for above sql query. — Pramod, Jan 12 '16 at 13:59
The closest ORM analogue I can imagine is `ABC.objects.annotate(Sum('amount'), Count(date)).values('phone_num', 'amount__sum', 'date__count')`, but I'm not really sure what we should do with other fields. Show the last one in group? A first one? It looks really strange to me. — Alex Morozov, Jan 12 '16 at 14:04
@Pramod Could you describe in words (without sql or python examples) what data you want to collect? — Andriy Ivaneyko, Jan 12 '16 at 14:59
@AndriyIvaneyko I am interested in id, user, name, phone_num, date, amount. — Pramod, Jan 12 '16 at 17:12
what count(date) means and supposes to return and what that exprassion means? — Andriy Ivaneyko, Jan 12 '16 at 17:16
@AndriyIvaneyko, date is just example here, it is fetching number of rows for each unique phone number. — Pramod, Jan 12 '16 at 17:18
The solution proposed by @AlexMorozov is the one that will do the job. In order to get the other fields, you just need to iterate over the queryset, and, for each `phone_num`, fetch the corresponding `ABC` objects (which may be more than one) — Andrea Corbellini, Jan 12 '16 at 17:40
@AndreaCorbellini Instead of iterating the code to fetch data for each field, It is better to use raw SQL. I just want my code to be django ORM compliant. — Pramod, Jan 12 '16 at 17:57
@Pramod: then go with raw SQL. What you want can't be done with the ORM, and this is a known limitation — Andrea Corbellini, Jan 12 '16 at 22:05

score 0 · Answer 1 · edited May 23 '17 at 12:04

Not sure whether it possible to grub data you mentioned above ( Select *, sum(amount), count( date ) by simple order by, probab;y that's JOIN query, at least you could try variants below and perform some intersection by phone_num on ABC.all():

ABC.objects.values("phone_num").order_by().annotate(count = Count("date"), amount= Sum("amount"))

Notes:

values('phone_num') - for GROUP BY 'phone_num' clause.
order_by() - for exclusion possible default ordering which ( you could remove that order_by().

p.s. Also try to run query below:

ABC.objects.all().values("phone_num").annotate(count = Count("date"), amount= Sum("amount"))

Update

You could do next loop to grub desired data as Django ORM solution is absent:

data = (dict(o, data=ABC.objects.filter(phone_num=o['phone_num'])[:1][0]) for o in ABC.objects
    .values("phone_num")
    .order_by()
    .annotate(count = Count("date"), amount= Sum("amount")).all())
// know you could access your data in next way:
for item in data:
    phone_num = item['phone_num']
    count = item['count']
    amount = item['amount']
    id = item['data'].id
    name = item['data'].name
    // Do other staff...

Note data formed with generator expression(comprehension)

This will only fetch phone_num from the table, I need to fetch all fields, any idea on that. — Pramod, Jan 12 '16 at 17:54
I have updated answer, idea-- you need equvalent of join on itself. — Andriy Ivaneyko, Jan 12 '16 at 17:55

Django ORM for given group by SQL query with aggregation method sum and count

1 Answers1