id summary reporter owner description type status component version severity resolution keywords cc stage has_patch needs_docs needs_tests needs_better_patch easy ui_ux 28599 Case/When seems to ungroup the results of a values().annotate() query Eric Palmitesta nobody "I've been reading https://docs.djangoproject.com/en/1.11/topics/db/aggregation/ and https://docs.djangoproject.com/en/1.11/ref/models/conditional-expressions/ and I can't tell if this is a bug, or if I'm just using annotate() incorrectly, but I've asked on [https://stackoverflow.com/questions/45802068 StackOverflow], [https://groups.google.com/forum/#!topic/django-users/Q276DNBY5vc django-users] and #django on freenode, and so far no one seems to have any suggestions. Let's say I have an Order model, and I want a payment breakdown grouped by user. Here's the Order model: {{{ class Order(models.Model): CASH = 'c' CARD = 'a' PAYMENT_TYPES = ( (CASH, 'Cash'), (CARD, 'Card'), ) user = models.ForeignKey(User, on_delete=models.PROTECT, null=True, blank=True) payment_type = models.CharField(max_length=1, choices=PAYMENT_TYPES) grand_total = models.DecimalField(max_digits=8, decimal_places=2) }}} Here's a values() + annotate() query showing me the total per user: {{{ query = Order.objects.values( 'user' ).annotate( total=Sum('grand_total'), ) }}} The result, so far so good: {{{ User Total -------------- User 1 300 User 2 250 }}} However, when I add Case/When conditions to the query: {{{ query = Order.objects.values( 'user' ).annotate( cash=Case(When(payment_type=Order.CASH, then=Sum('grand_total')), default=Value(0)), card=Case(When(payment_type=Order.CARD, then=Sum('grand_total')), default=Value(0)), total=Sum('grand_total'), ) }}} I get this result, which is not what I want: {{{ User Cash Card Total ---------------------------------- User 1 300 0 300 User 2 200 0 200 User 2 0 50 50 }}} Of course, this is what I want: {{{ User Cash Card Total ---------------------------------- User 1 300 0 300 User 2 200 50 250 }}} Is the Case/When undoing the GROUP BY that values() is giving me? Note: The Order model doesn't have default ordering, but just in case, upon reading https://docs.djangoproject.com/en/1.11/topics/db/aggregation/#interaction-with-default-ordering-or-order-by when using values(), I've tried adding `.order_by()` and `.order_by('user')` to my query, which did not change the result." Bug closed Database layer (models, ORM) 1.11 Normal invalid Unreviewed 0 0 0 0 0 0