Django: annotate(sum(case(when())))

user_11479719 0
Viewed 131

In my project, I'm trying to aggregate the data based on the status of a 'field'. The 'view' is expected to return a table like so:

SERVICE_CODE SUCCESS TECHNICAL_DECLINES BUSINESS_DECLINES
Service-1 S11 S12 S13
Service-2 S21 S22 S23

where S11,S12... are the aggregates taken based on the value of the field 'STATUS' in the model given below:

models.py

from django.db import models

class Wfmain(models.Model):
    ENTITY_NUM          = models.IntegerField()
    SRV_REF_NUM         = models.CharField(max_length=30,primary_key=True)
    ITER_SL             = models.IntegerField()
    STAGE_ID            = models.CharField(max_length=30)
    ACTION              = models.CharField(max_length=30)
    STATUS              = models.CharField(max_length=30)
    REQUEST_DATE        = models.DateField()
    SERVICE_CODE        = models.CharField(max_length=30)
    SERVICE_TYPE_CODE   = models.CharField(max_length=30)
    FUNCTION_CODE       = models.CharField(max_length=30)
    REQUEST_START_TIME  = models.DateField()

    class Meta:
         db_table = "WFMAIN"
         unique_together = (("ENTITY_NUM", "SRV_REF_NUM"),)

The aggregates ought to be grouped by the field 'SERVICE_CODE'

views.py

from django.db.models import When, Case, Sum, IntegerField
from django.shortcuts import render,redirect
from hr.models import *


def financial(request):
    S=['0','00','000']
    NT=['0','00','000','099','100','101','102','103','104','105','107','108','109','110','111','113','114','116','117','118','119','120','121','122',
        '123','124','180','181','182','183','184','185','186','187','188','189','200','201','205','213','217','218','219','220','221','222','223','224',
        '230','231','232','233','234','235','236','237','238','239','240','241','248','249','250','256','258','260','262','263','264','265']
    B=['S','099','100','101','102','103','104','105','107','108','109','110','111','113','114','116','117','118','119','120','121','122','123','124',
        '180','181','182','183','184','185','186','187','188','189','200','201','205','213','217','218','219','220','221','222','223','224','230','231',
        '232','233','234','235','236','237','238','239','240','241','248','249','250','256','258','260','262','263','264','265']
    wf=Wfmain.objects.using('MBDB')
    fin = wf.values('SERVICE_CODE').annotate( 
            Success=Sum(Case(When(STATUS in S, then=1),
                    when(STATUS not in S, then=0),
                    output_field=IntegerField())),
            Technical_declines=Sum(Case(When(STATUS not in NT, then=1),
                    When(STATUS in NT, then=0),
                    output_field=IntegerField())),
            Business_declines=Sum(Case(When(STATUS in B, then=1),
                    When(STATUS not in B, then=0),
                    output_field=IntegerField()))).order_by('SERVICE_CODE')

I'm stuck with error: "name 'STATUS' is not defined"

Output @browser:

enter image description here

Please tell me where I went wrong.

aggregate django
1 Answers

You need to translate the Case(When(…)) to a query named parameters. You thus define this with:

from django.db.models import Case, Value, When

fin = wf.values('SERVICE_CODE').annotate( 
    Success=Sum(Case(
        When(!STATUS__in=S, then=Value(1)),
        default=Value(0),
        output_field=IntegerField()
    )),
    Technical_declines=Sum(Case(
        When(STATUS__in=NT, then=Value(0)),
        default=Value(1),
        output_field=IntegerField()
    )),
    Business_declines=Sum(Case(
        When(STATUS__in=B, then=Value(1)),
        default=Value(0)
        output_field=IntegerField()
    ))
).order_by('SERVICE_CODE')
user_675790
Related