FB2.1 - aggregate function thoughts

[Poul Dige-3]

Some parts of this message have been removed. Learn more about Nabble's security policy.

If I do a

 

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

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 = 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 = 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 = 0 I will get the exact same result as in 1).

 

I would, however, expect that

 

  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.

 

Is the above correct behaviour of the db-engine?

 

Kind regards

Poul Dige

Tabulex

-------------------------------------------------------------------------
This SF.net email is sponsored by: Splunk Inc.
Still grepping through log files to find problems?  Stop.
Now Search log events and configuration files using AJAX and a browser.
Download your FREE copy of Splunk now >>  http://get.splunk.com/
_______________________________________________
Firebird-test mailing list
Firebird-test@...
https://lists.sourceforge.net/lists/listinfo/firebird-test

[Ann W. Harrison]

Poul,

    I think that Firebird is behaving consistently and following
the rules of the SQL standard (favoring the latter when the two
conflict).  However, you'll get more informed answers if you
send your question to firebird-support@...


Cheers,


Ann

-------------------------------------------------------------------------
This SF.net email is sponsored by: Microsoft
Defy all challenges. Microsoft(R) Visual Studio 2005.
http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
_______________________________________________
Firebird-test mailing list
Firebird-test@...
https://lists.sourceforge.net/lists/listinfo/firebird-test