Firebird With SUM() Different Than Same In MySQL

In MySQL, I wrote a SELECT statement that would summarize total number of therapies, presents and absences for each students ( using a GROUP BY to summarize as should be). The statement looks like this:

select numrecord,nombre,fechainformacion,programa,
referidopor,region,distrito,SUM(totalterapias),
SUM(ausente),SUM(suspendidad),fecharef,
fechaadm from infofact WHERE region=1
and (fechainformacion>='2004-03-01'
and fechainformacion<='2004-03-31')
and referidopor='SPECIAL EDUCATION'
GROUP BY numrecord,programa
ORDER BY region,referidopor,programa,distrito,nombre;

This works every time. But, when I tried to do the same thing in Firebird, Firebird complained that I did not have all the fields in the SELECT statement in my GROUP BY phrase. I only want to group the records captured and SUMmarized by numrecord (record number) and programa (program) so that the resulting subset of records has one record each for a specific student and the specific program he/she was in.

Do I have to use some sort of a sub-query to get this to work? And, if so, then when I've tried to set up what I think is a sub-query, Firebird complains about the inner SELECT.

Help.....New to FIREBIRD....

Vince

Limitations of GROUP BY:

When using GROUP BY, be aware of the following limitations:

Each column name that appears in a GROUP BY clause must also be specified in the SELECT

clause.

GROUP BY cannot specify a column whose values are derived from a mathematical,

aggregate, or user-defined function.

GROUP BY cannot be used in SELECT statements that:

· Contain an INTO clause (singleton selects).

· Use a subquery with a FROM clause which references a view whose definition contains

a GROUP BY or HAVING clause.

For each SELECT clause in a query, including subqueries, there can only be one GROUP BY

clause.



--
Best regards,
Fikret Hasovic http://fikret.fbtalk.net
TAMP R&D Team

FirebirdSQL Foundation member.
- Join today at http://www.firebirdsql.org/ff/foundation
JEDI VCS contributor
http://jedivcs.sourceforge.net/