title: django - annotate() - Sum() of a column with filter on another column
body: <p>I have the following two models.</p>
  
  <pre><code>class Product(models.Model):
      product_group=models.ForeignKey('productgroup.ProductGroup', null=False,blank=False)
      manufacturer=models.ForeignKey(Manufacturer, null=False,blank=False)
      opening_stock=models.PositiveIntegerField(default=0)
  
      class Meta:
          unique_together = ('product_group', 'manufacturer')
  </code></pre>
  
  <p>and</p>
  
  <pre><code>TRANSACTION_TYPE=(('I','Stock In'),('O','Stock Out'))
  class Stock(models.Model):
      product=models.ForeignKey('product.Product', blank=False,null=False)
      date=models.DateField(blank=False, null=False,)
      quantity=models.PositiveIntegerField(blank=False, null=False)
      ttype=models.CharField(max_length=1,verbose_name="Transaction type",choices=TRANSACTION_TYPE, blank=False)
  </code></pre>
  
  <p>I need to list all products with <code>stock_in_sum=Sum(of all stock ins)</code> , <code>stock_out_sum=Sum(of all stock outs)</code>  and <code>blance_stock=opening_stock+stock_in_sum - stock_out_sum</code></p>
  
  <p>This is what I've achieved so far.</p>
  
  <pre><code>class ProductList(ListView):
      model=Product
  
      def get_queryset(self):
          queryset = super(ProductList, self).get_queryset()
          queryset = queryset.prefetch_related('product_group','product_group__category','manufacturer')
          queryset = queryset.annotate(stock_in_sum = Sum('stock__quantity'))
          queryset = queryset.annotate(stock_out_sum = Sum('stock__quantity'))
  </code></pre>
  
  <p>I need to get </p>
  
  <ol>
  <li><code>stock_in_sum</code> as the <code>sum(quantity) where ttype='I'</code> </li>
  <li><code>stock_out_sum</code> as the <code>sum(quantity) where ttype='O'</code></li>
  <li><code>blance_stock</code> as <code>product.opening_stock + stock_in_sum - stock_out_sum</code></li>
  </ol>
  
  <p>along with each Product object.</p>
  
  <p>How do I achieve this?</p>
  
  <p>Thanks.</p>