Hi,

You will have to repeat the subqueries, cant use the alias of the subqueries for calculations, Firebird will assume that firstsum and secondsum belong to stores or costumers.

here's an example

select
(select sum(credit) from payments) as TotalCredits,
(select sum(debit) from payments) as TotalDebits,
(
(select sum(credit) from payments) -
(select sum(debit) from payments)
) as TotalIncome
from
payments

cheers

--- In IBObjects@yahoogroups.com, "Gordon Hamm" <GHamm@...> wrote:
>
> Hi,
> I know this isnt the right group, but Ive asked other places and am really in a bind.. Im desperate.
>
> Select S.name,S.Deptid,S.storeid,S.city,S.state,S.zone,S.County,
> (select Sum(OHA.Amount) from Ord_Head OHA where
> OHA.stores_rec_id=S.Rec_Id
> and (OHA.delivdate >= :startdateA)
> and (OHA.Delivdate <=:EnddateA)
> and OHA.void <> 'Y' As FirstSum,
>
> (select Sum(OHB.Amount) from Ord_Head OHB where
> OHB.stores_rec_id=S.Rec_Id
> and (OHB.delivdate >= :startdateB)
> and (OHB.Delivdate <= :EnddateB)
> and OHB.void <> 'Y'
> As SecondSum
>
> From Stores S,customers c
> Where S.cust_rec_id=C.rec_id
>
>
> This code works fine..
> But I want to add this code to get a difference of the 2 amounts as percent of the difference ...
>
> // I want a new column as Diff Amount
> (( SecondSum - FirstSum ) / FirstSum) * 100 as DiffAmt
>
> It complains that "FirstSum" and "SecondSum" are unknown..
>
> This giving me the sales percent difference between the 2 subqueries.. I
> need this becuase I need to sort the result set by percent difference..
> This query is used to give Year to Year sales differences..
>
> Is there some way of doing this?
>
>
>
>
>
>
>
>
> [Non-text portions of this message have been removed]
>