SQL Statement question, eliminating a field in Group by Clause

SQL Apprentice Question
Is there a way not to use Group By on some fields when that field is only in
the CASE. For example, in the statement below I do not want to use
InvoiceType in the CASE because I do not want it to be grouped by
InvoiceType. But I want a Total of all invoices, as Subtotal, if the type if
1 it is positive if it is 2 it is negative.


SELECT Products.ProductName,
SubTotal = CASE WHEN InvoiceType=1
THEN
SUM(ISNULL(CustomerInvoiceDetails.UnitPrice,0)*ISNULL(CustomerInvoices.Curr­encyRate,1)*ISNULL(CustomerInvoiceDetails.Quantity,0))
WHEN InvoiceType=2 THEN -SUM(ISNULL(CustomerInvoiceDetails.UnitPrice,0)*
ISNULL(CustomerInvoices.CurrencyRate,1)*ISNULL(CustomerInvoiceDetails.Quant­ity,0))
ELSE
SUM(ISNULL(CustomerInvoiceDetails.UnitPrice,0)*ISNULL(CustomerInvoices.Curr­encyRate,1)*ISNULL(CustomerInvoiceDetails.Quantity,0))
END


FROM [Customer Invoices] CustomerInvoices LEFT JOIN [Customer Invoice
Details] CustomerInvoiceDetails
ON CustomerInvoices.InvoiceID = CustomerInvoiceDetails.InvoiceID


GROUP BY Products.ProductName,InvoiceType



Celko Answers
>> Is there a way not to use GROUP BY on some fields [sic] when that field [sic] is only in the CASE [expression]. <<


You might want to read a book on how a GROUP BY works, on why columns
are not anything like fields and how a CASE expression works. I also
hope that you stop using the proprietary ISNULL, equal sign, etc. and
write standard SQL some day.

Looking at the code you posted, I have to ask why the heck did you
allow NULLs in the tables?? You are cleaning up more NULLs in one
query than I have seen in entire databases for major automobile
companies. Why do you have order without details, as shown by the LEFT
OUTER JOIN?


If your schema is that screwed up, you have real data integrity
problems.


SELECT P.product_name,
SUM (CASE I.invoice_type
WHEN 2
THEN -D.unit_price * I.currency_rate * D.quantity
ELSE D.unit_price * I.currency_rate * D.quantity
END) AS product_total


FROM CustomerInvoices AS I,
CustomerInvoiceDetails AS D,
Products AS P
WHERE I.invoice_id = D.invoice_id
AND D.product_id = P.product_id
GROUP BY P.product_name, I.invoice_type;


Of course you did not bother to post DDL, so this is a guess based on
that missing DDL.