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

Thread View: borland.public.interbase.sql

3 messages

[T] Back to Threads [V] Tree View [G] Group Articles

3 total messages Started by Olivier Peter Tue, 01 Jul 2008 09:30

#169

Author: Olivier Peter
Date: Tue, 01 Jul 2008 09:30

13 lines
1213 bytes

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?

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

#170

Author: Bill Todd [TeamB]
Date: Tue, 01 Jul 2008 13:57

23 lines
1619 bytes

Olivier Peter wrote:

> 
> 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?

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)

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

#171

Author: Olivier Peter
Date: Tue, 01 Jul 2008 14:19

30 lines
1883 bytes

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

Thanks Bill.


"Bill Todd [TeamB]" <no@no.com> wrote:
>Olivier Peter wrote:
>
>> 
>> 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?
>
>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)

Thread Navigation

This is a paginated view of messages in the thread with full content displayed inline.

Messages are displayed in chronological order, with the original post highlighted in green.

Use pagination controls to navigate through all messages in large threads.

Back to All Threads