Operand data type nvarchar is invalid for sum operator

Question

I tried to sum the data from my database using below code.

models.py

class Income(models.Model):
    Total= models.CharField(max_length=512,null =True,blank=True)

views.py :

Total_income = Income.objects.filter(Q(title='Salary')| Q(title='Bonus')).aggregate(Sum('Total'))

But I got this error when I try to run it.

('42000', '[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Operand data type nvarchar is invalid for sum operator. (8117) (SQLExecDirectW); [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared. (8180)')

I'm new to Python and Django so I really appreciate your help on this.

Which database and engine are you using? Can you share your DATABASES setting — Iain Shelvington
Actually I have figured out this error. I just changed my code in views.py to Total_income = Income.objects.filter(Q(title='Salary')| Q(title='Bonus')).annotate(as_float=Cast('Total', FloatField())).aggregate(Sum('as_float')) and it's work. But thanks anyway for reaching out to me hehe — nryaa2204

Sergey Pugach Sergey Pugach · Accepted Answer · 2020-06-16T05:21:42

You are trying to SUM CharField not Integer, that's why you see the error. So you need to make that field integer, or if for any reason you need it to left as CharField:

from django.db.models.functions import Cast

Income.objects.filter(
    Q(title='Salary')| Q(title='Bonus')
).annotate(
    total_as_float=Cast('Total', FloatField()
).aggregate(Sum('total_as_float'))

Operand data type nvarchar is invalid for sum operator

1 Answers