Hello all, and thanks for reading, I need to have a query that will sum union of sums, ie

SELECT SUM(Accounting.Paid) AS SumPaid, EXTRACT(MONTH FROM Accounting.Date) AS MonthNo
FROM Accounting
WHERE Accounting.Date BETWEEN '2008-1-1' AND '2008-10-1' AND EXTRACT(YEAR FROM
Accounting.Date) = 2008
Group By MonthNo
UNION ALL
SELECT SUM(OperateFees.Paid) AS SumPaid,  EXTRACT(MONTH FROM OperateFees.Date) AS MonthNo
FROM OperateFees
WHERE OperateFees.Date BETWEEN '2008-1-1' AND '2008-10-1' AND EXTRACT(YEAR FROM
OperateFees.Date) = 2008
Group By MonthNo

Now I need to sum SumPaid of the above result set
SELECT SUM(SumPaid), MonthNo  From ...

How can I do that

thanks

Khaled

What I need is to sum by MonthNo ie 1 , 2, 3, 4 etc
thanks

Khaled

"Khaled Homouda" <Khomouda@qualitynet.net> wrote in message
news:32918172-25B1-4066-BBA5-89BCE08D09AE@news.elevatesoft.com...
> Hello all, and thanks for reading, I need to have a query that will sum
> union of sums, ie
>
> SELECT SUM(Accounting.Paid) AS SumPaid, EXTRACT(MONTH FROM
> Accounting.Date) AS MonthNo

into memory\temp (only needed in the first select)

> FROM Accounting
> WHERE Accounting.Date BETWEEN '2008-1-1' AND '2008-10-1' AND EXTRACT(YEAR
> FROM
> Accounting.Date) = 2008
> Group By MonthNo
> UNION ALL
> SELECT SUM(OperateFees.Paid) AS SumPaid,  EXTRACT(MONTH FROM
> OperateFees.Date) AS MonthNo
> FROM OperateFees
> WHERE OperateFees.Date BETWEEN '2008-1-1' AND '2008-10-1' AND EXTRACT(YEAR
> FROM
> OperateFees.Date) = 2008
> Group By MonthNo;   <<<<<< script to be continued
>



> Now I need to sum SumPaid of the above result set
> SELECT SUM(SumPaid), MonthNo  From memory\temp
>

Robert

Khaled,

You can do that using a temporary table in a script:

DROP TABLE IF EXISTS "TempTbl" ;
SELECT SUM(Accounting.Paid) AS SumPaid, EXTRACT(MONTH FROM
Accounting.Date) AS MonthNo
INTO "TempTbl"
FROM Accounting
WHERE Accounting.Date BETWEEN '2008-1-1' AND '2008-10-1' AND
EXTRACT(YEAR FROM
Accounting.Date) = 2008
Group By MonthNo
UNION ALL
SELECT SUM(OperateFees.Paid) AS SumPaid,  EXTRACT(MONTH FROM
OperateFees.Date) AS MonthNo
FROM OperateFees
WHERE OperateFees.Date BETWEEN '2008-1-1' AND '2008-10-1' AND
EXTRACT(YEAR FROM
OperateFees.Date) = 2008
Group By MonthNo ;

SELECT SUM(SumPaid), MonthNo  From "TempTbl" GROUP BY MonthNo ;

--
Fernando Dias
[Team Elevate]

Thanks for your replies, then  should it be a different query? because the SQL I write is
in Query.SQL.ADD code updating monthNo or YearNo or DayNo and dates fro comboboxes

Khaled

"Khaled Homouda" <Khomouda@qualitynet.net> wrote in message
news:B36FBBEA-A425-423F-BC6D-E9C2BC48A2EC@news.elevatesoft.com...
> Thanks for your replies, then  should it be a different query? because the
> SQL I write is
> in Query.SQL.ADD code updating monthNo or YearNo or DayNo and dates fro
> comboboxes

No problem. Just make sure you end each step with ;

select ... from t;
select .... from t2;

etc.

Robert
>
> Khaled
>

"Khaled Homouda" <Khomouda@qualitynet.net> wrote in message
news:EB53B6A1-87F2-481F-B132-8FED42E35965@news.elevatesoft.com...
> What I need is to sum by MonthNo ie 1 , 2, 3, 4 etc
> thanks
>

The usual

select sum(money) mymoney, month
from memory\temp
group by  month

Robert
> Khaled
>

is it better to use UNION ALL or to force the items to be different by
adding some character?

Option 1

select a, b from t1
union all
select a, b from t2;

Option 2

select 1, a, b from t1
union
select 2, a, b from t2

seems to me option 1 is better, because you don't need to sort the result.
True?

Robert

Robert,

<< seems to me option 1 is better, because you don't need to sort the
result. True? >>

Yes.  UNION ALL is just a record dump, whereas UNION requires a temporary
index and some comparisons.

--
Tim Young
Elevate Software
www.elevatesoft.com