Small SQL question ...

Suppose I have table INVOICES and table LOOKUPS, with structures as follows:

INVOICES

CUSTOMER_NO      VARCHAR2(10)
PRODUCT_TYPE      VARCHAR2(10)
PRODUCT_NO         VARCHAR2(10)
UNITS_SOLD            NUMBER
AMOUNT                  NUMBER

and LOOKUPS

PRODUCT_DESC VARCHAR2(20)
PRODUCT_TYPE VARCHAR2(10) Is it possible to have a single SQL statement which will generate the sum of units sold, and amount for each customer, as well as as a subset based on product type?

IOW, the result should be

CUSTOMER NO TOTAL UNITS TOTAL AMT TOTAL BLADES SOLD TOTAL BLADES AMOUNT where I would find Blades-related products by searching LOOKUPS.

I am able to do this in two steps:

SELECT customer_no, SUM(units_sold), sum(amount), 0 "TOTAL BLADES SOLD", 0 "TOTAL BLADES AMOUNT"
FROM mytable
GROUP BY customer_no

and then
UPDATE mytable SET ("TOTAL BLADES SOLD", "TOTAL BLADES AMOUNT") =

    SELECT SUM(units_sold), SUM(amount)     FROM mytable a
    WHERE a.customer_no=mytable.customer_no     AND EXISTS (SELECT * from lookups b     WHERE a.product_code = b.product_code     AND b.product_type LIKE '%BLADE%')

My question is, is it possible to combine these two steps into one, possibly using a DECODE ?

TIA Pete Received on Sun Aug 09 1998 - 00:27:50 CDT