Getting Logical Error on Django ORM Query, the total is not matching with counts of all age group

`
class OPDStatReportView(ListAPIView):
    filter_backends = [DjangoFilterBackend]
    serializer_class = OPDStatReportSerializer
    filterset_class = OPDStatReportViewFilterSet

    def get_queryset(self):

        query_dict = {}
        for k, vals in self.request.GET.lists():
            if k != "offset" and k != "limit":
                if vals[0] != "":
                    k = str(k)
                    if k == "date_after":
                        k = "created_date_ad__date__gte"
                    elif k == "date_before":
                        k = "created_date_ad__date__lte"
                    query_dict[k] = vals[0]
                
        range_ages = (
            {"lookup": "lt", "label": "0-9", "age": (10,)},
            {"lookup": "range", "label": "10-14", "age": (10, 15)},
            {"lookup": "range", "label": "15-19", "age": (15, 20)},
            {"lookup": "range", "label": "20-59", "age": (20, 60)},
            {"lookup": "range", "label": "60-69", "age": (60, 70)},
            {"lookup": "gt", "label": ">70", "age": (70,)},
            {"lookup": "gt", "label": "total", "age": (0,)},
        )
        
        aggr_query = {}
        for item in range_ages:
            age = item.get("age")
            lookup = item.get("lookup")
            label = item.get("label")
            if lookup == "lt":
                aggr_query[label] = Count(Case(When(age__lt=age[0], then=1)))
            elif lookup == "gt":
                aggr_query[label] = Count(Case(When(age__gte=age[0], then=1)))
            elif lookup == "range":
                aggr_query[label] = Count(Case(When(age__range=age, then=1)))
        
        queryset_old = (
            CustomerVisit.objects.filter(follow_up_visit=True).annotate(
                age=(ExtractYear("created_date_ad") - ExtractYear("customer__user__dob_date_ad")) + 
                        (ExtractMonth("created_date_ad") - ExtractMonth("customer__user__dob_date_ad")) / 12 + 
                        (ExtractDay("created_date_ad") - ExtractDay("customer__user__dob_date_ad")) / 365,
                gender=F("customer__user__gender"),
                full_name=F("customer__user__full_name"),
            )
            .values("gender")
            .annotate(**aggr_query)
        ).filter(**query_dict)

        queryset_new = (
            CustomerVisit.objects.filter(follow_up_visit=False).annotate(
                age = (ExtractYear("created_date_ad") - ExtractYear("customer__user__dob_date_ad")) + 
                    (ExtractMonth("created_date_ad") - ExtractMonth("customer__user__dob_date_ad")) / 12 + 
                    (ExtractDay("created_date_ad") - ExtractDay("customer__user__dob_date_ad")) / 365,
                
                gender=F("customer__user__gender"),
                full_name=F("customer__user__full_name"),
            )
            .values("gender")
            .annotate(**aggr_query)
        ).filter(**query_dict)

        queryset = {
            "old": queryset_old,
            "new": queryset_new,
        }
        
        return queryset

    def list(self, request, *args, **kwargs):
        queryset = self.get_queryset()
        return Response(queryset)`

Question: Please look on this query. I am getting wrong output, While i am adding all the counts of different age groups the count is different that the total count, i.e. the count of age group from (0, ...), I am showing you the output below, so that you can calculate.

 "old": [
        {
            "gender": 1,
            "0-9": 64,
            "10-14": 37,
            "15-19": 24,
            "20-59": 507,
            "60-69": 206,
            ">70": 380,
            "total": 1218
        },
        {
            "gender": 2,
            "0-9": 39,
            "10-14": 20,
            "15-19": 41,
            "20-59": 885,
            "60-69": 294,
            ">70": 418,
            "total": 1693
        }
    ],
    "new": [
        {
            "gender": 1,
            "0-9": 1171,
            "10-14": 550,
            "15-19": 486,
            "20-59": 2152,
            "60-69": 577,
            ">70": 587,
            "total": 5203
        },
        {
            "gender": 3,
            "0-9": 0,
            "10-14": 0,
            "15-19": 0,
            "20-59": 3,
            "60-69": 0,
            ">70": 0,
            "total": 3
        },
        {
            "gender": 2,
            "0-9": 948,
            "10-14": 452,
            "15-19": 898,
            "20-59": 6019,
            "60-69": 997,
            ">70": 867,
            "total": 9385
        }
    ]
}

I think the problem is with calculation of age inside queryset, either a particular age is repeating twice or not getting included. I don't know what is happening. Please help me out to solve this issue.

ans + , i have added full_name in values i got a output where a particular was added in two age groups. let me show you.

This is the code i have changed:

queryset_new = (
    CustomerVisit.objects.filter(follow_up_visit=False).annotate(
        age = (ExtractYear("created_date_ad") - ExtractYear("customer__user__dob_date_ad")) + 
            (ExtractMonth("created_date_ad") - ExtractMonth("customer__user__dob_date_ad")) / 12 + 
            (ExtractDay("created_date_ad") - ExtractDay("customer__user__dob_date_ad")) / 365,
        
        gender=F("customer__user__gender"),
        full_name=F("customer__user__full_name"),
    )
    .values("gender", "full_name")
    .annotate(**aggr_query)
).filter(**query_dict)

This is the output which i got, and i was shocket, what a same person is getting added to 3 age groups. I am totall confused, Please sort me out. I think the problem is with calculating age.

{
    "gender": 2,
    "fullName": "Asmita Tamrakar",
    "0-9": 0,
    "10-14": 1,
    "15-19": 1,
    "20-59": 1,
    "60-69": 0,
    ">70": 0,
    "total": 2
},

if you need more information, please let me know.

Back to Top