Re: Group by problems

[Robert Singleton]

"Why is informix saying that codc needs to be in the group by statement, then, if I insert this column I get that nrprezi must be also in group by and so on. I just need a grouping by codfisc, because I need just one sum(taxa) for every distinct codfisc. Please help, 'cause I'm novice."

I am not sure about INFORMIX, but your SQL statement makes use of a * for selecting fields. Perhaps INFORMIX (unlike MySQL) works the same way Oracle does wherein you are required to include all fields in your GROUP BY clause that are not returned using GROUP BY functions.


Example:

SELECT s.id,s.first_name,s.last_name,AVG(t.score) as score FROM students s LEFT JOIN tests t on s.id=t.student_id GROUP BY s.id,s.first_name,s.last_name ORDER BY s.id;

In Oracle (as of v.8) you were required to include first_name and last_name in the GROUP BY clause even though you are grouping by the student's ID.

So far my experiences with MySQL suggest that MySQL does not force you to do this.

Hope this helps!