Django model - can't do aggregate sum with thousand comma separator and decimal points

Question

I have the following model:

class Example(models.Model):
    project_id = models.IntegerField(
        null=False, 
        blank=False, 
        default=0,
    )
    field1 = models.CharField(
        max_length=250,
        null=True, 
        blank=True,
    )
    field2 = models.CharField(
        max_length=250,
        null=True, 
        blank=True,
    )
    total = models.CharField(
        max_length=250,
        null=True, 
        blank=True,
    )

Example data:

project_id	field1	field2	total
1	1,323	4,234.55	5,557.55
2	1,000	2	1,002
3	1.23	3	4.23

total = field1 + field2

I would like to sum all total values.

This is what I've tried views.py:

context['total'] = Example.objects.filter(project_id=pid).aggregate(Sum('total'))

Current output:

{'total': 10.23}

Expected output:

{'total': 6,563.78}

Or, if that's not possible at least: 6563.78 so that I can format the numbers later.

Since the project requires thousand comma separator and decimal points, I can not change or alter the model fields and use FloatField.

Any help would be much appreciated

Answer 1

Since you say you can't change the data type on the fields themselves, you can achieve this by using the Replace and Cast database functions (of course, this isn't ideal - it would be better to fix the data types in the model itself).

This should work - at least it does on a production-grade database like PostgreSQL (I am not sure it will work on SQLite - if you are using that in production then you really have problems):

from django.db.models import FloatField, Value
from django.db.models.functions import Cast, Replace

context['total'] = Example.objects.annotate(
    cleaned_total=Replace('total', Value(','), Value(''))
).annotate(
    float_total=Cast('cleaned_total', FloatField())
).aggregate(Sum('float_total'))

What this is doing is:

1. Performing a replace on the field to remove commas.
2. Passing that cleaned value to a cast function that casts the cleaned strings as floats.
3. Performing a sum on the result.