I have the following statement:

SELECT SUM(Accounting.Paid) AS SumPaid, EXTRACT(MONTH FROM Accounting.Date) AS MonthNo
FROM Accounting
WHERE (EXTRACT(MONTH FROM Accounting.Date)) BETWEEN  2  AND 12
AND (EXTRACT(Year FROM Accounting.Date)) = 2006
Group By MonthNo

UNION

SELECT SUM(OperateFees.Paid) AS SumPaid, EXTRACT(MONTH FROM OperateFees.Date) AS MonthNo
FROM OperateFees
WHERE (EXTRACT(MONTH FROM OperateFees.Date)) BETWEEN  2  AND 12
AND (EXTRACT(Year FROM OperateFees.Date)) = 2006
Group By MonthNo


and I need to be able to sum MonthNo of both parts together to get one result SumPaid for 1 one MonthNo
thanks

Simply use a temporary table in the first select block, and perform another
Group on the temp table
and use Union ALL to ensure you do not lose any records in common
ie
Select ...
into Temp
from..
union all
Select
group by ..;

Select MonthNo, Sum(SumPaid) as SumPaid
from Temp
group by Monthno;

Donat.

"Khaled Homouda" <khomouda@qualitynet.net> wrote in message
news:947030E8-FEF9-4827-A7BE-4DC0E2B177E2@news.elevatesoft.com...
>I have the following statement:
>
> SELECT SUM(Accounting.Paid) AS SumPaid, EXTRACT(MONTH FROM
> Accounting.Date) AS MonthNo
> FROM Accounting
> WHERE (EXTRACT(MONTH FROM Accounting.Date)) BETWEEN  2  AND 12
> AND (EXTRACT(Year FROM Accounting.Date)) = 2006
> Group By MonthNo
>
> UNION
>
> SELECT SUM(OperateFees.Paid) AS SumPaid, EXTRACT(MONTH FROM
> OperateFees.Date) AS MonthNo
> FROM OperateFees
> WHERE (EXTRACT(MONTH FROM OperateFees.Date)) BETWEEN  2  AND 12
> AND (EXTRACT(Year FROM OperateFees.Date)) = 2006
> Group By MonthNo
>
>
> and I need to be able to sum MonthNo of both parts together to get one
> result SumPaid for 1 one MonthNo
> thanks
>

Thanks a lot

Khaled