How to filter and order by with Sum while keeping the summed data in Django

OK..I even do not know how to describe the situation in the title. But considerate below models:

class TheModel(models.Model):
  qty = models.IntegerField()
  name = models.CharField()
  type = models.CharField(choices=['A' ,'B', 'C'])

The requirement is to get the sum of qty and sort by sum of qty, but also has sum of A, B, C.

assume there are 5 rows in total:

name, qty, type
(name1, 3, A)
(name1, 3, B)
(name1, 4, C)
(name2, 5, A)
(name2, 10, C)

The result should be like this:

name  sum_qty, type A, type B, type C
name1    10      3       3       4
name2    15      5       0       10

Here in Django not have is any straightforward solution for this problem but i tried to solve it like this

models.py

class TheModel(models.Model):
    ch=(('A','A'),('B','B'),('C','C'))
    name = models.CharField(max_length=255)
    qty = models.IntegerField()
    ch_type = models.CharField(max_length=255,choices=ch)

views.py

from django.shortcuts import render
from .models import *
from django.db.models import Sum


def HomeView(request):
    all_names ={i.name for i in TheModel.objects.all()}
    all_choices = {choice[0] for choice in TheModel.ch}
    output = []
    for name in all_names:
      typeA = TheModel.objects.filter(name=name, ch_type='A')
      typeB = TheModel.objects.filter(name=name, ch_type='B')
      typeC = TheModel.objects.filter(name=name, ch_type='C')
      data = {
        'name': name,
        'typeA': typeA.aggregate(Sum('qty'))['qty__sum'] if (len(typeA) > 0) else 0,
        'typeB': typeB.aggregate(Sum('qty'))['qty__sum'] if (len(typeB) > 0) else 0,
        'typeC': typeC.aggregate(Sum('qty'))['qty__sum'] if (len(typeC) > 0) else 0,    
      }
      data['sum'] = data['typeA'] + data['typeB'] + data['typeC']
      output.append(data)   

    context = {
      'all_data':sorted(output, key=lambda d: d['name'])
    }
    return render(request,'index.html',context)

Html

<div class="container">
  <div class="row">
    <div class="col-lg-12">
      <table class="table table-sm">
        <thead>
          <tr>
            <th scope="col">Name</th>
            <th scope="col">TypeA</th>
            <th scope="col">TypeB</th>
            <th scope="col">TypeC</th>
            <th scope="col">Sum</th>
          </tr>
        </thead>
        <tbody>

          {% for i in all_data %}
          <tr>
            <td>{{i.name}}</td>
            <td>{{i.typeA}}</td>
            <td>{{i.typeB}}</td>
            <td>{{i.typeC}}</td>
            <td>{{i.sum}}</td>
          </tr>
          {% endfor %}

        </tbody>
      </table>
    </div>
  </div>
</div>

Output in Html Table

enter image description here

admin panel output

enter image description here

Back to Top