Error: Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause)

Question

I am using Firebird SQL. The below mentioned query returns 4 rows as shown in the figure.

    SELECT a.EPS_ID,b.C_NAME,c.AY_YR_NAME,d.S_NAME,e.E_NAME
FROM 
    TBLEXAMPLANNER_S_MSB a, 
    TBLCLASS_MSB b, 
    TBLACADEMICYEAR_MSB c, 
    TBLSUBJECTS_MSB d, 
    TBLEXAMTYPE_MSB e
WHERE 
    a.EPS_CLASS_ID=b.C_ID 
AND a.EPS_SESSION_ID=c.AY_ID 
AND a.EPS_SUB_ID=d.S_ID 
AND a.EPS_PE_ID=e.E_ID

I want it to return only 1(one) row like

EPS_ID     C_NAME    AY_YR_NAME   S_NAME   E_NAME
---------------------------------------------------
7          5         2016-2017    English  FA1 

I am using the following query but it does not work.

SELECT a.EPS_ID,MAX(b.C_NAME) AS XT,c.AY_YR_NAME,d.S_NAME,e.E_NAME
FROM 
    TBLEXAMPLANNER_S_MSB a, 
    TBLCLASS_MSB b, 
    TBLACADEMICYEAR_MSB c, 
    TBLSUBJECTS_MSB d, 
    TBLEXAMTYPE_MSB e
WHERE 
    a.EPS_CLASS_ID=b.C_ID 
AND a.EPS_SESSION_ID=c.AY_ID 
AND a.EPS_SUB_ID=d.S_ID 
AND a.EPS_PE_ID=e.E_ID
GROUP BY a.EPS_ID,d.S_NAME

The error message is :

Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause)

Solution

The usage of GROUP BY makes the engine group the records for you. To do grouping, you have to give advice to the RDBMS for each column, what it should do.

• Group it? -> Add column to GROUP BY-Clause
• Not group it? -> ok, what else?
  • ignore the column? remove it from your select-clause
  • Sum it? -> use SUM(mycol)
  • other aggregation functions can be found in the documentation

Additionally: In your case you try to group by EPS_ID, which is unique in each row. So a grouping by that column will return all rows, because there is nothing to group by. To group records, they have to have the same value.

Comments

[Cyber Space]

Thanks @gordon but it still shows me 4 rows as shown in the attached image

[Toby Speight]

Although this code may help to solve the problem, it doesn't explain why and/or how it answers the question. Providing this additional context would significantly improve its long-term educational value. Please edit your answer to add explanation, including what limitations and assumptions apply.