Django query to get List of Sums of the Count of different Choices-CodePudding

I am trying to get a list of the sum of the count of different choices. The choices are strings, so I want to count the same strings and get their sum, and then do the same for other choices. I made a query but it does not give me the total count of each choice instead list them all with the count 1.

model.py

class sublist(models.Model):
      Music = 'Music'   
      Video = 'Video'
      Gaming = 'Gaming'
      News = 'News'
      Lifestyle = 'Lifestyle'
      Access = 'Access'

      SUBTYPE_CHOICES =(
     (Music , "Music"),
     (Video , "Video"),
     (Gaming , "Gaming"),
     (News , "News"),
     (Lifestyle , "Lifestyle"),
     (Access , "Online Access"),
     )
     author = models.ForeignKey(settings.AUTH_USER_MODEL, on_delete=models.CASCADE,)
     name = models.CharField(max_length=150)
     cost = models.FloatField(default = 0)
     subtype = models.CharField(max_length= 50, choices = SUBTYPE_CHOICES, default = Access)

This is my query. (i tried coming up with other combinations by looking at the documentation but no luck)

expenselist = sublist.objects.filter(author = curruser.id)
subtypecount = list((expenselist
                .annotate(subcount=Count('subtype'))
                .values('subtype', 'subcount')
                ))

result of query above: [{'subtype': 'Access', 'subcount': 1}, {'subtype': 'Access', 'subcount': 1},{'subtype': 'Video', 'subcount': 1}]

Desired result: [{'subtype': 'Access', 'subcount': 2},{'subtype': 'Video', 'subcount': 1}]

CodePudding user response：

Here is the query that will produce your desired result. First use .values() then .annotate()

subtypecount = sublist.objects.filter(author = curruser.id).values('subtype').annotate(subcount=Count('subtype'))

expenselist = sublist.objects.filter(author = curruser.id)
subtypecount = list(expenselist.values('subtype').annotate(subcount=Count('subtype')))