Sum query

[Jim]

Im trying to make this into one select statement witout using
variables..

declare @sum1 int
declare @sum2 int
declare @sum3 int

select @sum1 = sum(CLIENT_PLAN NED_SERVICE.Tot alUnits)from
client_planned_ service


select @sum2 = sum(RECORDED_SE RVICE.UNITS)
from recorded_servic e
where (RECORDED_SERVI CE.SERVICE_STAT US_MONIKER)is null or
RECORDED_SERVIC E.SERVICE_STATU S_MONIKER='FB07 BA368454433A989 B840BDCE7340F'
group by RECORDED_SERVIC E.SERVICE_STATU S_MONIKER

set @sum3 = @sum1 - @sum2

select @sum3

is there a way? When I try this..

select (sum(CLIENT_PLA NNED_SERVICE.To talUnits) -
sum(RECORDED_SE RVICE.UNITS)) as summary
from recorded_servic e, client_planned_ service
where (RECORDED_SERVI CE.SERVICE_STAT US_MONIKER)is null or
RECORDED_SERVIC E.SERVICE_STATU S_MONIKER='FB07 BA368454433A989 B840BDCE7340F'
group by RECORDED_SERVIC E.SERVICE_STATU S_MONIKER

I get two records (because of the 2 condifitions in the where
clause)...I need one..

thanks

-Jim

[Simon Hayes]

Re: Sum query


"Jim" <jim.ferris@mot orola.com> wrote in message
news:729757f9.0 404260857.7b61b 3dc@posting.goo gle.com...[color=blue]
> Im trying to make this into one select statement witout using
> variables..
>
> declare @sum1 int
> declare @sum2 int
> declare @sum3 int
>
> select @sum1 = sum(CLIENT_PLAN NED_SERVICE.Tot alUnits)from
> client_planned_ service
>
>
> select @sum2 = sum(RECORDED_SE RVICE.UNITS)
> from recorded_servic e
> where (RECORDED_SERVI CE.SERVICE_STAT US_MONIKER)is null or
> RECORDED_SERVIC E.SERVICE_STATU S_MONIKER='FB07 BA368454433A989 B840BDCE7340F'
> group by RECORDED_SERVIC E.SERVICE_STATU S_MONIKER
>
> set @sum3 = @sum1 - @sum2
>
> select @sum3
>
> is there a way? When I try this..
>
> select (sum(CLIENT_PLA NNED_SERVICE.To talUnits) -
> sum(RECORDED_SE RVICE.UNITS)) as summary
> from recorded_servic e, client_planned_ service
> where (RECORDED_SERVI CE.SERVICE_STAT US_MONIKER)is null or
> RECORDED_SERVIC E.SERVICE_STATU S_MONIKER='FB07 BA368454433A989 B840BDCE7340F'
> group by RECORDED_SERVIC E.SERVICE_STATU S_MONIKER
>
> I get two records (because of the 2 condifitions in the where
> clause)...I need one..
>
> thanks
>
> -Jim[/color]

Here's one possible way:

select sum(Total) as 'GrandTotal'
from
(
select sum(CLIENT_PLAN NED_SERVICE.Tot alUnits) as 'Total'
from client_planned_ service
union all
select sum(RECORDED_SE RVICE.UNITS)
from recorded_servic e
where (RECORDED_SERVI CE.SERVICE_STAT US_MONIKER)is null or
RECORDED_SERVIC E.SERVICE_STATU S_MONIKER='FB07 BA368454433A989 B840BDCE7340F'
group by RECORDED_SERVIC E.SERVICE_STATU S_MONIKER
) dt

Simon