-
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.
-
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.
-
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
-
error
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?
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|