SQL for Smarties | SQL Programming Style | Trees and Hierarchies in SQL | SQL Puzzles and Answers | Data and Databases

Monday, June 12, 2006

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
WHEN InvoiceType=2 THEN -SUM(ISNULL(CustomerInvoiceDetails.UnitPrice,0)*

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

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

SELECT P.product_name,
SUM (CASE I.invoice_type
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.

No comments: