Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause / ORA-00979: not a GROUP BY expression / ORA-00937: not a single-group group function

This type of error occurs in one of the following situations

A. When "GROUP BY" is used in your sql, all the columns in the SELECT or in the ORDER BY clause must also be included in the  GROUP BY clause unless they are in an aggregate function such as AVG, MAX, MIN, COUNT, SUM, etc. Otherwise the system returns the error "Column '<your column>' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause".

      For example
      SELECT c.NAME, f.FlightNo
      FROM Company c
      INNER JOIN Flight f on c.ID = f.COMID
      GROUP BY c.NAME, f.FlightNo
      ORDER BY c.NAME, f.FlightNo, f.FlightTime;

      will generate such an error  "Column 'FlightTime' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause" because the system does not know which FlightTime to use from the group for ordering the records.

      To fix the error, do one of the followings.

      1. Add the column causing the error to the GROUP BY clause

      SELECT c.NAME, f.FlightNo
      FROM Company c
      INNER JOIN Flight f on c.ID = f.COMID
      GROUP BY c.NAME, f.FlightNo,  f.FlightTime
      ORDER BY c.NAME, f.FlightNo, f.FlightTime;

      2. Make it an aggregate function if it is possible

      SELECT c.NAME, f.FlightNo
      FROM Company c
      INNER JOIN Flight f on c.ID = f.COMID
      GROUP BY c.NAME, f.FlightNo
      ORDER BY c.NAME, f.FlightNo, MIN(f.FlightTime);

      3. Remove the column from the SELECT and ORDER BY clause if it does not affect the result significantly

      SELECT c.NAME, f.FlightNo
      FROM Company c
      INNER JOIN Flight f on c.ID = f.COMID
      GROUP BY c.NAME, f.FlightNo
      ORDER BY c.NAME, f.FlightNo;

B. When the SELECT has an aggregate function as well as a ordinary column name, and the sql does not have a GROUP BY clause.

      For example 
      SELECT dep.Name, count(emp.ID)
      FROM department dep
      INNER JOIN employee emp on dep.ID = emp.depID;

      will generate such an error  "Column 'dep.Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause" because the system does not know how to count the emp.ID. 

      To fix the error, add a GROUP BY clause to the query and put the column causing the error in the GROUP BY clause so that the system counts the emp.ID by dep.Name. All employees with the same depID are counted into one result.

      SELECT dep.Name, count(emp.ID)
      FROM department dep
      INNER JOIN employee emp on dep.ID = emp.depID
      GROUP BY dep.NAME;

-------------------------------------------------------------------------------------------------------------------------------------------------------            
                        
If you have ever asked yourself these questions, this is the book for you. What is the meaning of life? Why do people suffer? What is in control of my life? Why is life the way it is? How can I stop suffering and be happy? How can I have a successful life? How can I have a life I like to have? How can I be the person I like to be? How can I be wiser and smarter? How can I have good and harmonious relations with others? Why do people meditate to achieve enlightenment? What is the true meaning of spiritual practice? Why all beings are one? Read the book free here.