RelationalDBDesign RelationalDBDesign


Data Manipulation   «Prev 

Summarize using DECODE

Suppose you are at House-O-Pets and you want to see total sales you had in each month of the year. You want a report where you have the month name across the top and the sum of that month's sales below it. Here is how to do it using DECODE. The example shows only three months for year 2000, to shorten the code:

SELECT 
SUM(DECODE(TO_CHAR(SALES_DATE,'MM'),'01',TOTAL_SALE_AMOUNT,0))
 January,
SUM(DECODE(TO_CHAR(SALES_DATE,'MM'),'02',TOTAL_SALE_AMOUNT,0))
 February,
SUM(DECODE(TO_CHAR(SALES_DATE,'MM'),'03',TOTAL_SALE_AMOUNT,0))
 March
FROM CUSTOMER_SALE
WHERE TO_CHAR(SALES_DATE,'YYYY') = '2000'

The results look like this:
JANUARY FEBRUARY MARCH --------- --------- --------- 0 20.47 108.03

Looking closely at the DECODE function, you can see that each column in the results adds all rows up. The difference between the columns is that certain rows are set to zero in each DECODE. For example, for the January column, if the sale was made in January, the number returned by the DECODE function is equal to the TOTAL_SALES_AMOUNT. Otherwise, the number returned is zero.
In effect, you are filtering out all sales amounts other than the ones in January. The other two query expressions work the same way, except that the second one looks for February and the third one looks for March. Imagine the possibilities of this powerful tool.