Subject RE: [ib-support] group by on derived columns
Author Thomas Steinmaurer
> The Interbase docs say...
> Each column name that appears in a GROUP BY clause must also be
> specified in
> the SELECT
> clause.
> and
> GROUP BY cannot specify a column whose values are derived from a
> mathematical,
> aggregate, or user-defined function.
> Is this still the case in Firebird 1.0? (it does appear to be)
> I have a query that includes the following derived column...
> sp.spdate + (7 - extract(weekday from sp.spdate))
> ...this returns the week ending date for individual dates in the query. I
> need to be able to group on this column to generate week ending totals. Is
> there a way to do this in a straight select query? I can do it
> via a stored
> proc. but I'm trying to avoid that since my app. code constructs straight
> select queries dependent on user choices and I don't want to
> break that if I
> don't have to.

This can only be done by either creating a view or using a COMPUTED BY

Thomas Steinmaurer
IB LogManager 2.0 - The Logging/Auditing Tool for InterBase and Firebird