title: Django equivalent of sum+case
body: <p>I have a bunch of django models</p>
  
  <pre><code>class ReviewItem(Model):
    review = models.ForegnKey("Review")
    person = models.ForeignKey("Person")
    category = models.ForeignKey("Category")
    item = models.ForeignKey("item")
    reviewed = models.DateTimeField(null=True)
  
  class Person(Model):
    name = models.CharField(max_length=255)
  
  class Category(Model):
    name = models.CharField(max_length=127)
  
  class Item(Model):
    name = models.CharField(max_length=127)
    category = models.ForeignKey("Category")
  </code></pre>
  
  <p>(As you can see, the "Category" fk in ReviewItem is redundant)</p>
  
  <p>There will be at most NxM ReviewItem records where N is the number of people and M is the number of items that they might have assigned to them, and they will have their "reviewed" date set after they've been reviewed.  Items are grouped into categories.</p>
  
  <p>What I want is a count of how many items for each item have been reviewed and how many have not.  In SQL, I could do</p>
  
  <pre><code>select category.name, item.name,
  sum(case when reviewed is null then 1 else 0 end) as un_reviewed
  sum(case when reviewed is null then 0 else 1 end) as reviewed
  from reviewitem
  join  category on category.id = reviewitem.category_id
  join  item on item.id = reviewitem.item_id
  group by category.id, item.id
  order by category.name, item.name
  </code></pre>
  
  <p>I can't figure out how to do this without doing two separate QuerySets in django.</p>
  
  <p>Doing it with two QuerySets, I ended up with:</p>
  
  <pre><code>uncompleted_items = Item.objects.filter(
      reviewitem__review=current_review,
      reviewitem__person__reports_to=eff_user,
      reviewitem__reviewed__isnull=True
  ).select_related(
      'category',
  ).annotate(num_uncompleted=Count('reviewitem'))
  
  completed_items = Item.objects.filter(
      reviewitem__review=current_review,
      reviewitem__person__reports_to=eff_user,
      reviewitem__reviewed__isnull=False
  ).select_related(
      'category',
  ).annotate(num_completed=Count('reviewitem'))
  </code></pre>