Django annotate with casted variable

Question

i want to achieve something complex with a django queryset. My model looks like this:

class Mymodel(models.Model):
    #Model Variables
    year = models.IntegerField()
    month = models.IntegerField()
    date = models.DateField(_("Date"), default=datetime.date.today)
    score = models.CharField(max_length=50)

Unfortunately,

score' can't change from CharField() to FloatField()

, it should be kept as a String.

What i need is to sum all scores by year and month. I tried something like this to group all scores with no great success.

values = Mymodel.objects.all().values(
    'year', 'month').annotate(Sum('score')).order_by('year', 'month')

I tried to cast 'score' before use annotate by doing this.

values = Mymodel.objects.all().annotate(
        scoreFloat=Cast('score', FloatField())
    ).values('year', 'month').annotate(
        Sum('scoreFloat')).order_by('year', 'month')

Once again with no success, since i am getting a KeyError for scoreFloat parameter.

Any suggestions?

Answer 1

In your second query you should add to the values new field from first annotate:

Mymodel.objects.annotate(as_float=Cast('score', FloatField())
    ).values('year', 'month', 'as_float'
    ).order_by('year', 'month'
    ).annotate(sumscore=Sum('as_float')).values('year', 'month', 'sumscore')