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 |
