Sum, Decode + Reworking a Group By
SQL can be tricky. Here’s a way to display date based summations across one row of output…
Suppose you have quarterly totals for 2006 like this:
SELECT TO_CHAR(orderdt, 'Q') Q, sum (price) the_total
FROM my_orders
WHERE orderdt >= '01-JAN-2006'
AND orderdt < '01-JAN-2007'
GROUP BY TO_CHAR(orderdt, 'Q')
ORDER BY 1;
The results would be like this:
Q THE_TOTAL
- -----------
1 1000
2 1500
3 1300
4 2000
Now let’s throw some SUM and DECODE functions into the mix.
SELECT
SUM (DECODE(TO_DATE(orderdt, 'Q'), '1', sale_price, 0)) Q1,
SUM (DECODE(TO_DATE(orderdt, 'Q'), '2', sale_price, 0)) Q2,
SUM (DECODE(TO_DATE(orderdt, 'Q'), '3', sale_price, 0)) Q3,
SUM (DECODE(TO_DATE(orderdt, 'Q'), '4', sale_price, 0)) Q4
FROM my_orders
WHERE orderdt > '01-JAN-2006'
AND orderdt < '01-JAN-2007'
GROUP BY TO_CHAR(orderdt, 'Q');
Results would then look formated I think how you would like:
Q1 Q2 Q3 Q4
---------- ---------- ---------- ----------
1000 1500 1300 2000
