SQL Query Help !!

[madar1987]

hi,
i am trying to run the following query

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.

[PAVB]

... and the question is?

[LKBrwn_DBA]

First of all, your syntax is incorrect, use this as starters:

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');

[madar1987]

ORA-00979: not a GROUP BY expression

am getting the above error :-(
plz help

[DaPi]

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?

[madar1987]

found the solution ..

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

[DaPi]

WOW ! ! ! ! You have a solution.

Such a shame that no one on this planet knows what problem it solves.

Shades of Douglas Adams . . . .

[gandolf989]

WOW ! ! ! ! You have a solution.

Such a shame that no one on this planet knows what problem it solves.

Shades of Douglas Adams . . . .

The answer is 42!!!

[DaPi]

The answer is 42!!!

We all know that!
What is the question?

[gandolf989]

We all know that!
What is the question?

I think I recall Douglas Adams saying "What is 6 times 7"