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

Thread: Group by function

  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 06:27 PM.

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    I don't see a join condition between the two tables: lwc a, ldt b
    Have I missed it? Is that intentional?
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  3. #3
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Group by function

    No, there is no join between the two tables but I did use 2 tables because I need different data from the 2 tables.

    Thanks,
    You do realise that will generate an initial result set (before grouping) which will contain all possible pairings of rows from the two tables? (Cartesian Product)? That might explain why you're out by a factor of four in one case. I'd start thinking about that before looking any further for errors.


    P.S. use the "Post Reply" to keep everything in the same thread.
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  4. #4
    Join Date
    Jul 2000
    Posts
    2
    I need to use group by because I am using other queries with union function.

    Thanks,

  5. #5
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by kiranmayi
    I need to use group by because I am using other queries with union function.
    I don't doubt that - but it's only by breaking down ("analysing") your query that you'll fix it. You should start by looking at the result of this:
    Code:
    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'
    Put the result in a table (or a spread-sheet) so that you can play with it. If that's not right, the rest can never be.
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  6. #6
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    DOH ! !
    I do not want to get the second row in the result . . . without anything under STATE column
    Does this mean you just need to add to the WHERE clause:
    AND state = 'TX'
    ? ? ? ?

    Did the presence of the other four states "multilply data by 4." ?
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

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