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.
