Re: Decode Experts - Help

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Thu, 05 Nov 1998 15:00:16 GMT
Message-ID: <3642bcc5.2396666@dcsun4.us.oracle.com>

On Thu, 05 Nov 1998 03:01:56 GMT, lakkundi_at_msn.com wrote:

>A client wants us to list the number of occurrences of holidays
>celebrated by quarter from
>
>the holiday table. (Holiday table eg. below)
>
>HOLIDAY ACTUALDAT CELEBRATE
>------------------------- --------- ---------
>NEW YEAR DAY 01-JAN-95 01-JAN-95
>MARTIN KING, JR. 15-JAN-95 16-JAN-95
>LINCOLNS BIRTHDAY 12-FEB-95 20-FEB-95
>VALENTINES DAY 14-FEB-95 14-FEB-95
>MEMORIAL DAY 30-MAY-95 29-MAY-95
>
>The output should have column headings that are Q1, Q2, Q3 etc. and look
>like the following:
>
>Title Q1 Q2 Q3 Q4 Total
>Holiday Occurrence 2 5 2 1 10
>

  1 select

  2    sum( decode( to_char(d,'q'), 1, 1, null ) ) Q1,
  3    sum( decode( to_char(d,'q'), 2, 1, null ) ) Q2,
  4    sum( decode( to_char(d,'q'), 3, 1, null ) ) Q3,
  5    sum( decode( to_char(d,'q'), 4, 1, null ) ) Q4,

  6 count(d) total
  7* from foo
SQL> /         Q1 Q2 Q3 Q4 TOTAL ---------- ---------- ---------- ---------- ----------

         4          1                                5

SQL> select * from foo;

D

---

01-JAN-95
16-JAN-95
20-FEB-95
14-FEB-95
29-MAY-95

>
>Any tips greatly appreciated.
>
>I have tried but it does not well:
>sum(decode(to_char(CELEBRATEDDATE, 'Q'),1,NULL)) Q1,
>sum(decode(to_char(CELEBRATEDDATE, 'Q'),2,1,NULL)) Q2
>
>Thanks&#137;
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Thu Nov 05 1998 - 09:00:16 CST