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

Thread: SQL Query Help !!

Hybrid View

  1. #1
    Join Date
    Sep 2009
    Posts
    3

    Exclamation SQL Query Help !!

    hi,
    i am trying to run the following query

    Code:
    insert into monthly values(SELECT DISTINCT AVG(MEM),DNAME,AVG(CPU),VDATE,AVG(DISKU),COUNT(*) FROM MS WHERE TO_NUMBER(TO_CHAR(VD
    ATE, 'MM')) = TO_NUMBER(TO_CHAR(TRUNC(SYSDATE), 'MM'))-1 GROUP BY DNAME,TO_CHAR(VDATE,'MM')
    What i want to achieve is to get the average of performance parameters over a month and insert it into another table with same table definition.

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    ... and the question is?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Cool Bad, bad syntax...

    First of all, your syntax is incorrect, use this as starters:
    Code:
    INSERT INTO monthly
       SELECT DISTINCT AVG (mem), dname, AVG (cpu), vdate, AVG (disku), COUNT (*)
                  FROM ms
                 WHERE TO_NUMBER (TO_CHAR (vdate, 'MM')) =
                                    TO_NUMBER (TO_CHAR (TRUNC (SYSDATE), 'MM'))
                                    - 1
              GROUP BY dname, TO_CHAR (vdate, 'MM');
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  4. #4
    Join Date
    Sep 2009
    Posts
    3

    Exclamation error

    ORA-00979: not a GROUP BY expression

    am getting the above error :-(
    plz help

  5. #5
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    You don't need DISTINCT - the grouping will ensure that.

    The SELECT and GROUP BY need the same expression in them. You have vdate and TO_CHAR (vdate, 'MM').
    Which brings us to the real point: as the two tables have the same definition and vdate is presumably a DATE, how do you want to represent a month? e.g. should it be as 1st September 2009?

    Finally, do you really want to add together data for August 2009, August 2008, August 2007, etc etc?
    Last edited by DaPi; 09-22-2009 at 04:18 AM.
    "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
    Sep 2009
    Posts
    3
    found the solution ..

    i need to use a group function in the select statement
    hence using a max(vdate) in select solved the issue

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