How to count and make addition with for loop with a table in Django?

I created a customer system. In this system, a user has several customers. I have a Customer model and this model has multiple fields like risk_rating, credit_limit, country, etc…

I want to list the countries where customers are located and show data about them in a data table. I am using .distinct() method for that, but I cannot figure out how can I show some data.

For example, I want to total credit_limit (adding operation) in the related country and I want to count all customers that have Low risk risk_rating.

I can do this the long way with if statement. But I can’t think of anything other than just creating a concatenated if statement for all countries. This would be a very long and inefficient way. Because there are 208 countries in the world.

How can I do that?

Note: I try to use for loop counter for counting the number of risks but it did not work properly.

models.py

class Customer(models.Model):
    RISK_RATING = [
        ('Low Risk', 'Low Risk'),
        ('Medium Risk', 'Medium Risk'),
        ('Moderately High Risk', 'Moderately High Risk'),
        ...
    ]

    customer_name = models.CharField(max_length=100)
    country = models.ForeignKey(Country, on_delete=models.CASCADE, null=True, unique=False)
    credit_limit = models.FloatField(default=0, null=True)

    risk_rating = models.CharField(max_length=50, default='Select', choices=RISK_RATING, null=True)
    created_date = models.DateTimeField(auto_now_add=True)

views.py

def risk_context_processor(request):
    current_user = request.user
    userP = UserProfile.objects.get_or_create(username=current_user)
    customer_list = Customer.objects.filter(company=userP[0].company)
    countries = Customer.objects.values_list('country__country_name', flat=True).distinct()

    country_links = Country.objects.filter()
    iso_country_links = Customer.objects.values_list('country__country_name', flat=True).distinct()

    codes = pycountry.countries.get(name='American Samoa').alpha_2

    context = {
        'customer_list': customer_list,
        'countries': countries,
        'country_links': country_links,
        'iso_country_links': iso_country_links,
        'codes': codes,
    }

    return context

template.html

<table id="multi-filter-select" class="display table table-striped table-hover grid_" >
                        <thead>
                           <tr>
                              <th>Country</th>
                              <th>Low Risk</th>
                              <th>Medium Risk</th>
                              <th>Moderately High Risk</th>
                              <th>High Risk</th>
                              <th>Very High Risk</th>
                              <th>Strict Credit Check</th>
                              <th>No Credit Check</th>
                              <th>Credit Limit</th>
                           </tr>
                        </thead>
                        <tbody>
                           {% for country in countries %}
                           <tr>
                              <td>
                                 {% for country_link in country_links %}
                                     {%  if country_link.country_name == country %}
                                         <img src="{{ country_link.country_flag.url }}" width="30px" height="20px"/>
                                         <a href="/country/{{ country_link.id }}/customers">{{country}}</a>
                                     {% endif %}
                                 {% endfor %}
                              </td>
                              <td>
                                 {% for customer in customer_list %}
                                    {% if customer.country.country_name == country %}
                                        {% if customer.risk_rating == "Low Risk" %}
                                                    {{ forloop.counter }}
                                        {% endif %}
                                    {% endif %}
                                 {% endfor %}
                              </td>
                              <td>
                                 {% for customer in customer_list %}
                                     {% if customer.country.country_name == country %}
                                         {% if customer.risk_rating == "Medium Risk" %}
                                            {{ forloop.counter }}
                                        {% endif %}
                                    {% endif %}
                                 {% endfor %}
                              </td>
                              <td>
                                 {% for customer in customer_list %}
                                    {% if customer.country.country_name == country %}
                                        {% if customer.risk_rating == "Moderately High Risk" %}
                                            {{ forloop.counter }}
                                        {% endif %}
                                    {% endif %}
                                 {% endfor %}
                              </td>
                              <td>
                                 {% for customer in customer_list %}
                                    {% if customer.country.country_name == country %}
                                        {% if customer.risk_rating == "High Risk" %}
                                            {{ forloop.counter }}
                                        {% endif %}
                                    {% endif %}
                                 {% endfor %}
                              </td>
                              <td>
                                 {% for customer in customer_list %}
                                     {% if customer.country.country_name == country %}
                                        {% if customer.risk_rating == "Very High Risk" %}
                                            {{ forloop.counter }}
                                        {% endif %}
                                     {% endif %}
                                 {% endfor %}
                              </td>
                              <td>
                                 {% for customer in customer_list %}
                                    {% if customer.country.country_name == country %}
                                        {% if customer.risk_rating == "Strict Credit Check" %}
                                            {{ forloop.counter }}
                                        {% endif %}
                                    {% endif %}
                                 {% endfor %}
                              </td>
                              <td>
                                 {% for customer in customer_list %}
                                    {% if customer.country.country_name == country %}
                                        {% if customer.risk_rating == "No Credit Check" %}
                                            {{ forloop.counter }}
                                        {% endif %}
                                    {% endif %}
                                 {% endfor %}
                              </td>
                              <td>
                                 {% for customer in customer_list %}
                                    {% if customer.country.country_name == country %}
                                        {{ customer.credit_limit }}
                                    {% endif %}
                                 {% endfor %}
                              </td>
                           </tr>
                           {% endfor %}
                        </tbody>
                     </table>

And this is my table

Answer

 Try this,

from django.db.models import Count, Sum

Customer.objects.values("country", "country__name", "country__alpha_2").annotate(
    **{
        f"{stored.lower().replace(' ', '_')}_count": Count(
            "risk_rating", filter=Q(risk_rating=stored)
        )
        for (stored, displayed) in Customer.RISK_RATING
    },
    total_credit_limit=Sum("credit_limit"),
).order_by("country").distinct("country")

This will yield objects with the following fields,

[“country id”, “country name”, “country codes i.e. alpha_2”, “low_risk_count”, “medium_risk_count”, …, “total_credit_limit”]

Update #1:

To access it in the template, use something like this.

<table>
<tr>
    <th>Country</th>
    <th>Total Customers</th>
    <th>Low Risk</th>
    <th>Medium Risk</th>
    <th>Moderate High Risk</th>
    <th>...</th>
    <th>Total Credit Limit</th>
</tr>
{% for country in countries %}
    <tr>
        <td>{{ country.country__name }}</td>
        <td>{{ country.total_customers }}</td>
        <td>{{ country.low_risk_count }}</td>
        <td>{{ country.medium_risk_count }}</td>
        <td>{{ country.moderate_high_risk_count }}</td>
        <td>{{ country.... }}</td>
        <td>{{ country.total_credit_limit }}</td>
    </tr>
{% endfor %}
</table>

Update #2:

from django.db.models import Count, Sum

Customer.objects.values("country", "country__name", "country__alpha_2").annotate(
    **{
        f"{stored.lower().replace(' ', '_')}_count": Count(
            "risk_rating", filter=Q(risk_rating=stored)
        )
        for (stored, displayed) in Customer.RISK_RATING
    },
    total_credit_limit=Sum("credit_limit"),
    total_customers=Count("id")
).order_by("country").distinct("country")