Using Aggregate functions in CASE When seletion 2

[memarkiam]

I have a table which (simplified) has

CustomerID
PerdioNo (YYYYMM)
Amount

and therefore has multiple rows per CustomerID, if they have activity on several periods.

I would like to transform this into 1 row per CustomerID, with:

CustomerID, 1999Amount, 2000Amount, 2001Amount

I have been able to split the amounts into 3 columns accross, but not sum them to 1 row. I'm using:

select
address.CUSTOMER_ID,
CASE When SUBSTR(customer_period_totals.PERIOD_ID,1,4) = '1999' then (customer_period_totals.AMOUNT_1 - customer_period_totals.AMOUNT_2) END,
CASE When SUBSTR(customer_period_totals.PERIOD_ID,1,4) = '2000' then (customer_period_totals.AMOUNT_1 - customer_period_totals.AMOUNT_2) END,
CASE when substr(customer_period_totals.PERIOD_ID,1,4) = '2001' then (customer_period_totals.AMOUNT_1 - customer_period_totals.AMOUNT_2) END
from address, customer_period_totals
where address.CUSTOMER_ID = customer_period_totals.CUSTOMER_ID
order by address.customer_id

If I try to use the sum function inside the CASE statement (changing the ORDER BY to GROUP BY I get an 'ORA-00979: not a group by expression' just after the SUBSTR( in the case expression.

Any idea how I can do this??!!!

Many, many thaks,
Mark
London

 

[OraCool]

Try This:

select
address.CUSTOMER_ID,
sum(CASE When SUBSTR(customer_period_totals.PERIOD_ID,1,4) = '1999' then (customer_period_totals.AMOUNT_1 - customer_period_totals.AMOUNT_2) END),
sum(CASE When SUBSTR(customer_period_totals.PERIOD_ID,1,4) = '2000' then (customer_period_totals.AMOUNT_1 - customer_period_totals.AMOUNT_2) END),
sum(CASE when substr(customer_period_totals.PERIOD_ID,1,4) = '2001' then (customer_period_totals.AMOUNT_1 - customer_period_totals.AMOUNT_2) END)
from address, customer_period_totals
where address.CUSTOMER_ID = customer_period_totals.CUSTOMER_ID
group by address.customer_id
order by address.customer_id;

 

[rac2]

Or make a VIEW for each year

CREATE VIEW amounts_1999
AS
SELECT address.CUSTOMER_ID, SUM(customer_period_totals.AMOUNT_1 - customer_period_totals.AMOUNT_2)) AS "1999Amount"
FROM address, customer_period_totals
WHERE address.CUSTOMER_ID = customer_period_totals.CUSTOMER_ID
AND SUBSTR(customer_period_totals.PERIOD_ID,1,4) = '1999' 
GROUP BY address.CUSTOMER_ID

Then LEFT JOIN them for the columner display

SELECT  address.CUSTOMER_ID, amounts_1999.1999Amount, amounts_2000.2000Amount,amounts_2001.2001Amount
FROM address
LEFT JOIN amounts_1999 ON address.CUSTOMER_ID = amounts_1999.CUSTOMER_ID
LEFT JOIN amounts_2000 ON address.CUSTOMER_ID = amounts_2000.CUSTOMER_ID
LEFT JOIN amounts_2001 ON address.CUSTOMER_ID = amounts_2001.CUSTOMER_ID

 

[OraCool]

Did you try the script?

It is simpler to solve with SQL than creating views for every variation of a report requirement. You will end up with countless views. Then documentation and maintenance of these views will take up all your time.

 

[memarkiam]

Thank you both very much!!

The info on creating veiws was very helpful, and has opened up options for me in other queries I need to write, but in this case, improving the syntax to embed the aggregate funtion allows me to complete this in 1 step.

Great!

One follow up, if I may...
Do you know if I can use a column alias within the CASE clause. I don't need it for this, but for another query, where I do need to store the result in a temporary view, I am getting column name errors from automatically picking up the first few characters of the clause.

Cheers!!
Mark

 

[memarkiam]

Sorry - I just answered it myself! You put the as ... after the END, not by each WHEN clause.

Easy when you know how!