1

I have a query as below which returns the grade of all students of a specific class in a specific term(semester) in a specific session(academic year):

grades = Grade.objects.filter(term='First', student__in_class=1,session=1).order_by('-total')

then another query that annotate through the grades in order to get the sum of the 'total' field.

grades_ordered = grades.values('student')\
    .annotate(total_mark=Sum('total')) \
    .order_by('-total_mark')

At first everything works fine until when i migrated from using SQLite to postgreSQL then the following error begins to show up.

ERROR:

function sum(character varying) does not exist
LINE 1: SELECT "sms_grade"."student_id", SUM("sms_grade"."total") AS...
                                         ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

EDIT: here is my model

class Grade(models.Model):
    session = models.ForeignKey(Session, on_delete=models.CASCADE)
    term = models.CharField(choices=TERM, max_length=7)
    student = models.ForeignKey(Student, on_delete=models.CASCADE)
    subject = models.ForeignKey(Subject, on_delete=models.CASCADE)
    fca = models.CharField(max_length=10)
    sca = models.CharField(max_length=10)
    exam = models.CharField(max_length=10)
    total = models.CharField(max_length=3, blank=True, null=True)
    grade = models.CharField(choices=GRADE, max_length=1, blank=True, null=True)
    remark = models.CharField(max_length=50, blank=True, null=True)

any help you can provide would be appreciated.

thanks

4
  • 1
    Please post your model, it looks like the total field type may not support arithmetic operations. Commented Feb 21, 2019 at 12:09
  • @mfrackowiak: I edited the post which now includes the model. Commented Feb 21, 2019 at 12:15
  • While I definitely with point in the answer, you can also check this question stackoverflow.com/questions/50733159/… Commented Feb 21, 2019 at 12:18
  • thank you @mfrackowiak; it appears as you stated the total field is CharField in my case which will not support arithmmatic operations Commented Feb 21, 2019 at 12:26

1 Answer 1

6

Store numbers in integer or decimal not in text/varchar field

total = models.Integer(max_length=3, blank=True, null=True)

see this link

also read this

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.