[Firebird-test] FB2.1 - aggregate function thoughts

[Poul D. <pd...@ta...>]

If I do a=20

1)
select
   a.activity_date,
   sum(a.endtime-a.starttime)/3600 as man_hours_this_day
from activitytable a
group by activity_date

I will get the expected result, i.e. in my case the total man hours =
spent on activities each date.

If I do a=20
2)
select
   a.activity_date,
   CONSTANT + sum(a.endtime-a.starttime)/3600 as man_hours_this_day
from activitytable a
group by activity_date

setting the constant to e.g. 10, there will  be added 10 hours to the =
result each day, just as I expect.

If, however, the constant is defined in a separat table

3)
select
   a.activity_date,
   c.constant + sum(a.endtime-a.starttime)/3600 as man_hours_this_day
from activitytable a
inner join constanttable c on c.id =3D a.constant_id
group by activity_date

then I get the error message

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

If I then add the c.constant to the group by clause
4)
select
   a.activity_date,
   c.constant + sum(a.endtime-a.starttime)/3600 as man_hours_this_day
from activitytable a
inner join constanttable c on c.id =3D a.constant_id
group by activity_date, c.constant

I get the same result as in 1) except that the constant being added to =
each day. Assuming the constant =3D 0 I will get the exact same result =
as in 1).

I would, however, expect that=20

  c.constant + sum(a.endtime-a.starttime)/3600 as man_hours_this_day

is as a whole an aggregate function because a part of it is an aggregate =
function.=20

Is the above correct behaviour of the db-engine?

Kind regards
Poul Dige
Tabulex