Printable View
hi,
i am trying to run the following query
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.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')
... and the question is?
First of all, your syntax is incorrect, use this as starters:
:rolleyes: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');
ORA-00979: not a GROUP BY expression
am getting the above error :-(
plz help
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?
found the solution ..
i need to use a group function in the select statement
hence using a max(vdate) in select solved the issue :)
WOW ! ! ! ! You have a solution.
Such a shame that no one on this planet knows what problem it solves. :confused:
Shades of Douglas Adams . . . .
The answer is 42!!! :DQuote:
Originally Posted by DaPi
We all know that!Quote:
Originally Posted by gandolf989
What is the question?
I think I recall Douglas Adams saying "What is 6 times 7" ;)Quote:
Originally Posted by DaPi