?
Solved

ORA-00979: not a GROUP BY expression

Posted on 2006-05-29
3
Medium Priority
?
1,171 Views
Last Modified: 2012-09-21
Any idea why i get the following error when i try to run this SQL query?

SQL> CREATE VIEW EventReportMonthly AS
  2  SELECT
  3   to_char(EStart,'YYYY MM') AS TYearMonth,
  4   to_char(EStart - INTERVAL '6' MONTH,'MM') AS TFinMonth,
  5   to_char(EStart - INTERVAL '6' MONTH,'YY') AS TFinYear,
  6   count(*) AS TNumEvents,
  7   sum(EAttendance) AS TTotalAttendance,
  8   sum(EHireFee) AS TTotalHireFee
  9  FROM EventR
 10  GROUP BY to_char(EStart,'MM/YYYY')
 11  ORDER BY to_char(EStart,'YYYY-MM');
 to_char(EStart,'YYYY MM') AS TYearMonth,
         *
ERROR at line 3:
ORA-00979: not a GROUP BY expression
0
Comment
Question by:sneak_186
  • 2
3 Comments
 
LVL 12

Accepted Solution

by:
jwahl earned 200 total points
ID: 16787637
try this:

SQL> CREATE VIEW EventReportMonthly AS
  2  SELECT
  3   to_char(EStart,'YYYY MM') AS TYearMonth,
  4   to_char(EStart - INTERVAL '6' MONTH,'MM') AS TFinMonth,
  5   to_char(EStart - INTERVAL '6' MONTH,'YY') AS TFinYear,
  6   count(*) AS TNumEvents,
  7   sum(EAttendance) AS TTotalAttendance,
  8   sum(EHireFee) AS TTotalHireFee
  9  FROM EventR
 10  GROUP BY
        to_char(EStart,'YYYY MM'),
        to_char(EStart - INTERVAL '6' MONTH,'MM'),
        to_char(EStart - INTERVAL '6' MONTH,'YY')
 11  ORDER BY to_char(EStart,'YYYY-MM');
0
 

Author Comment

by:sneak_186
ID: 16787816
Thanks!! It worked perfectly.
Is that the standard format for a GROUB BY statement (ie you have to group by every attribute that isnt a total)?
Thanks again.

sneak_186
0
 
LVL 12

Expert Comment

by:jwahl
ID: 16787840
yes.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses
Course of the Month14 days, 15 hours left to enroll

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question