At 12:16 PM 9/07/2008, you wrote:

>Does Firebird 2.1 support sub queries?
>If not how would you write the following.
>
>Assume that we have a single table
>
>CREATE TABLE TEST1 (
> ID INTEGER NOT NULL,
> CUSTCODE INTEGER NOT NULL,
> INTDATE DATE NOT NULL,
> INTVOLUME BIGINT NOT NULL,
> INTVALUE NUMERIC(18,4) NOT NULL,
> PRIMARY KEY (ID, CUSTCODE)
>);
>
>I want to run a query that will bring the following results
>
>1) sum all volumes from a period easy enough
>
> Select CUSTCODE, Sum(VOLUME) From Test1
> WHERE INDATE >= '2007-01-01' AND INTDATE <='2007-03-30'
> GROUP BY CUSTCODE;
>2) Return the sum of volumes for an other period or from the start up
> to but not including the '2007-01-01'.
> something like
>
> SELECT T1.CUSTCODE, SUM(T1.VOLUME),
> (SELECT SUM(T2.VOLUME) FROM TEST1
> WHERE T2.INTDATE < '2007-01-01'
> AND T2.CUSTCODE = T1.CUSTCODE)
> From TEST1 T1
> WHERE T1.INTDATE >= '2007-01-01' AND T1.INTDATE <='2007-03-30'
> GROUP BY CUSTCODE;
>How would you do something like this in firebird?

Of course Firebird supports subqueries of myriad kinds. However, I think your immediate problems here are not due to subqueries but to unacceptable syntax for the aggregation and some aliasing errors.

Firebird is very strict about grouping - you can't get away with including fields (even derived ones) in the SELECT specification if they are not involved in the aggregation. (If you have "history" with InterBase or Firebird 1.0.x, you would have been allowed to do it, with unpredictable results.)

However, if I understand your requirements correctly, you DO want the historical total in the grouping, i.e., you actually want to aggregate on CUSTCODE for *both* of these totals.

If so, try this:

SELECT
T1.CUSTCODE,
SUM(T1.VOLUME) as Q1_2007_VolumeTotal,
SUM (
SELECT T2.VOLUME FROM TEST1 T2 -- !!!!!
WHERE T2.INTDATE < '2007-01-01'
AND T2.CUSTCODE = T1.CUSTCODE
) as PastVolumeTotal

From TEST1 T1
WHERE T1.INTDATE BETWEEN '2007-01-01' AND '2007-03-31'
/* GROUP BY T1.CUSTCODE; This is OK */
GROUP BY 1; -- Sometimes better where multi-sets are involved

./heLen