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.
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.
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