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
The example shows only three months for year 2000, to shorten the code:
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.