👀 Views: 0 💬 Answers: 1 📅 Created: 2025-06-12

django queryset aggregation postgresql python

I'm integrating two systems and I've tried everything I can think of but I'm working on a personal project and I'm working on a reporting feature in my Django 4.1 application where I need to aggregate data from multiple related models... My main model is `Order`, which has a foreign key to `Customer`. I want to generate a queryset that provides the total number of orders and the total revenue generated for each customer. So far, I have this code: ```python from django.db.models import Count, Sum from .models import Order customer_report = Order.objects.values('customer__name').annotate( total_orders=Count('id'), total_revenue=Sum('amount') ) ``` However, I'm running into an scenario where the `total_revenue` is returning `None` for some customers. I've checked the `amount` field in the `Order` model, and it is correctly set as DecimalField with null=False. I'm also using `Django 4.1` with PostgreSQL. I tried to add a filter to exclude orders with a zero amount: ```python customer_report = Order.objects.exclude(amount=0).values('customer__name').annotate( total_orders=Count('id'), total_revenue=Sum('amount') ) ``` This didn't resolve the scenario as I still see `None` for total revenue in some cases. I suspect it might be due to some customers not having any orders at all, but I want to include those customers in the report with a total of `0`. How can I modify my query to ensure that customers with no orders still appear in the results with a total order count and revenue of zero instead of `None`? Any insights would be greatly appreciated! My development environment is macOS. How would you solve this? For reference, this is a production service. This issue appeared after updating to Python LTS. Thanks for your help in advance! This is part of a larger desktop app I'm building.