Is it possible to aggregate over differing where clauses?

Posted by BenAlabaster on Stack Overflow See other posts from Stack Overflow or by BenAlabaster
Published on 2010-12-22T18:46:09Z Indexed on 2010/12/22 18:54 UTC
Read the original article Hit count: 145

Filed under:
|
|

Is it possible to calculate multiple aggregates based on differing where clauses? For instance:

Let's say I have two tables, one for Invoice and one for InvoiceLineItems. The invoice table has a total field for the invoice total, and each of the invoice line item records in the InvoiceLineItems table contains a field that denotes whether the line item is discountable or not. I want three sum totals, one where Discountable = 0 and one where Discountable = 1 and one where Discountable is irrelevant. Such that my output would be:

InvoiceNumber  Total  DiscountableTotal  NonDiscountableTotal
-------------  -----  -----------------  --------------------
1              53.27  27.27              16.00
2              38.94   4.76              34.18
3...

The only way I've found so far is by using something like:

Select i.InvoiceNumber,
       i.Total,
       t0.Total As DiscountableTotal,
       t1.Total As NonDiscountableTotal
From   Invoices i
    Left Join (
        Select   InvoiceNumber,
                 Sum(Amount),
        From     InvoiceLineItems
        Where    Discountable = 0
        Group By InvoiceNumber
    ) As t0 On i.InvoiceNumber = t0.InvoiceNumber
    Left Join (
        Select   InvoiceNumber,
                 Sum(Amount)
        From     InvoiceLineItems
        Where    Discountable = 1
        Group By InvoiceNumber
    ) As t1 On i.InvoiceNumber = t1.InvoiceNumber

This seems somewhat cumbersome, it would be nice if I could do something like:

Select   InvoiceNumber,
         Sum(Amount) Where Discountable = 1 As Discountable
         Sum(Amount) Where Discountable = 0 As NonDiscountable
Group By InvoiceNumber

I realize that SQL is completely invalid, but it logically portrays what I'm trying to do...

TIA

P.S. I need this to run on a SQL Server 2000 instance, but I am also interested (for future reference) if/how I would achieve this on SQL Server 2005/2008.

© Stack Overflow or respective owner

Related posts about sql

Related posts about sql-server