Cyber Space Cyber Space - 2 months ago 8
SQL Question

Annoying GROUP BY clause

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


click to view image
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)

swe swe
Answer

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, waht 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