CODEWITHSUNDEEP
pythonsqldjangogroup-bydjango-queryset
Edoardo Zucchelli
Edoardo Zucchelli

Reputation: 77

Django ORM Query with 'having' and 'group by'

This is the raw sql query I'm trying to write in Django ORM.

    SELECT cf, sum(fee)
        FROM public.report
        where report_date = '2021-11-01'
        group by cf
        having sum(fee) > 500000

I've tried this, but I miss the having part:

Report.objects.filter(report_date=date_to).values('cf').annotate(Sum('fee'))

I've also tried this, but here I miss the other part to group by fiscal code.

Report.objects.filter(report_date=date_to).aggregate(fee=Sum('fee', filter=Q(fee__gte=50000)))

I need to join this 2, to make a unique query.

Upvotes: 0

Views: 299

Answers (1)

Amrez
Amrez

Reputation: 629

Django query:

Report.objects \
    .filter(report_date=date_to) \
    .values('cf') \
    .annotate(total_fee=Sum('fee')) \
    .filter(total_fee__gte=500000)

Result:

SELECT "core_report"."cf", SUM("core_report"."fee") AS "total_fee"
FROM "core_report"
WHERE "core_report"."report_date" = 2021-11-01
GROUP BY "core_report"."cf"
HAVING SUM("core_report"."fee") >= 500000

Explanation of what i did

Upvotes: 1

Related Questions