Group by function
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Group by function

Threaded View

  1. #1
    Join Date
    Jul 2000
    Posts
    2

    Group by function

    Hello I am having a problem with the following query.
    PHP Code:
    SELECT  STATE,sum(decode(rep_month,'JANUARY'cntnull)) "JAN-05",
    sum(decode(rep_month,'FEBRUARY'cntnull)) "FEB-05",
    Sum(decode(rep_month,'MARCH'cntnull)) "MAR-05",
    sum(decode(rep_month,'APRIL'cntnull)) "APR-05",
    sum(decode(rep_month,'MAY'cntnull)) "MAY-05",
    sum(decode(rep_month,'JUNE'cntnull)) "JUN-05",
    sum(decode(rep_month,'JULY'cntnull)) "JUL-05",
    sum(decode(rep_month,'AUGUST'cntnull)) "AUG-05",
    sum(decode(rep_month,'SEPTEMBER'cntnull)) "SEP-05",
    sum(decode(rep_month,'OCTOBER'cntnull)) "OCT-05",
    sum(decode(rep_month,'NOVEMBER'cntnull)) "NOV-05",
    sum(decode(rep_month,'DECEMBER'cntnull)) "DEC-05"
    from (select decode(state,'TX','ZDA Target','OK','','MO','',
    'KS','','AR',''"STATE",
    decode(dep_month,1,'JANUARY',2,'FEBRUARY',3,'MARCH',4,'APRIL',5,'MAY',6,'JUNE',7,'JULY',8,'AUGUST',9,'SEPTEMBER',10,'OCTOBER',11,'NOVEMBER',12,'DECEMBER'rep_month,target_value cnt
    from lwc a
    ,ldt b
    where DMA_STATE_IND
    ='STATE' and DA_LU_IND='DA' and a.region='TEMP' 
    GROUP BY STATE,dep_month,target_valueGROUP BY STATE;


    output for the query is:

    STATE          JAN-05     FEB-05     MAR-05     APR-05     MAY-05     JUN-05
    ---------- ---------- ---------- ---------- ---------- ---------- ----------
        
    JUL-05     AUG-05     SEP-05     OCT-05     NOV-05     DEC-05
    ---------- ---------- ---------- ---------- ---------- ----------
    ZDA Target          0          0         27         55        191        273
           273        327        354        409        409        409

                        0          0        108        220        764       1092
          1092       1308       1416       1636       1636       1636 
    I do not want to get the second row in the result i.e.

    0 0 108 220 764 1092 1092 1308 1416 1636 1636 1636, with out anything under STATE column. It looks like it did multilply data by 4.

    Any ideas?

    Thanks,
    Kiran
    Last edited by tamilselvan; 05-09-2005 at 07:27 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width