How to summarize (and group) the result of a union?

[Olivier Peter]

I want to make a sum of amount grouped by year, month, week and day based on the result set of a union.
However, when i try the following i get an error on line three token unknown SELECT.

SELECT YEAR, MONTH, WEEK, DAY, SUM(AMOUNT) FROM
(
SELECT YEAR, MONTH, WEEK, DAY, AMOUNT FROM TABLE1
UNION
SELECT YEAR, MONTH, WEEK, DAY, AMOUNT FROM TABLE2
)
GROUP BY YEAR, MONTH, WEEK, DAY

Any ideas how to make this work?

[Bill Todd [TeamB]]

Olivier Peter wrote:

InterBase does not support derived tables. Create a view with the UNION
then select from the view. Unless you want duplicates dropped use UNION
ALL for better performance.

--
Bill Todd (TeamB)

[Olivier Peter]

I've been thinking about a view too, but i was still hoping for a more flexible solution.

Thanks Bill.