adding two sum() 'ed values

Hi all,
if I have
select sum(ammount) from claimfinancialpayment where claimid = 10
select sum(ammount) from claimpaymenthistory where claimid = 10
How can I add the two summed values together, when
select sum(ammount) from claimfinancialpayment where claimid = 10
sums three rows,
and
select sum(ammount) from claimpaymenthistory where claimid = 10
sums 5 rows
I was thinking of
declare @.tot numeric(12,2), @.sum1 numeric(12,2), @.sum2 numeric(12,2)
select @.sum1 = sum(ammount) from claimfinancialpayment where claimid = 10
select @.sum2 = sum(ammount) from claimpaymenthistory where claimid = 10
set @.tot = @.sum1+@.sum2
Would this be correct or is there an easier or faster way of doing this
THanks
RObertRobert
SELECT SUM(bblala)
FROM
(
select sum(ammount) as blbla from claimfinancialpayment where claimid = 10
UNION ALL
select sum(ammount) from claimpaymenthistory where claimid = 10
) AS Der
"Robert Bravery" <me@.u.com> wrote in message
news:OWgV$BQQGHA.4680@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> if I have
> select sum(ammount) from claimfinancialpayment where claimid = 10
> select sum(ammount) from claimpaymenthistory where claimid = 10
> How can I add the two summed values together, when
> select sum(ammount) from claimfinancialpayment where claimid = 10
> sums three rows,
> and
> select sum(ammount) from claimpaymenthistory where claimid = 10
> sums 5 rows
> I was thinking of
> declare @.tot numeric(12,2), @.sum1 numeric(12,2), @.sum2 numeric(12,2)
> select @.sum1 = sum(ammount) from claimfinancialpayment where claimid = 10
> select @.sum2 = sum(ammount) from claimpaymenthistory where claimid = 10
> set @.tot = @.sum1+@.sum2
> Would this be correct or is there an easier or faster way of doing this
> THanks
> RObert
>
>|||You should use UNION instead of UNION ALL there.
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uahTBFQQGHA.140@.TK2MSFTNGP12.phx.gbl...
> Robert
> SELECT SUM(bblala)
> FROM
> (
> select sum(ammount) as blbla from claimfinancialpayment where claimid = 10
> UNION ALL
> select sum(ammount) from claimpaymenthistory where claimid = 10
> ) AS Der
>
>
> "Robert Bravery" <me@.u.com> wrote in message
> news:OWgV$BQQGHA.4680@.TK2MSFTNGP10.phx.gbl...
>|||If both SELECTs return the same value, you will lose one of them using UNION
instead of UNION ALL as a duplicate will be discarded.
BTW Uri, you appear to have mistyped the column name for the outer SUM, your
query will error due to it trying to sum column bblala when the only column
is blbla.
SELECT SUM(blabla)
FROM
(
select sum(ammount) as blabla from claimfinancialpayment where claimid =
10 UNION ALL select sum(ammount) from claimpaymenthistory where claimid =
10 ) AS Der
Dan
Roji. wrote on Mon, 6 Mar 2006 15:23:42 +0530:
> You should use UNION instead of UNION ALL there.
> --
> Regards
> Roji. P. Thomas
> http://toponewithties.blogspot.com
> "Uri Dimant" <urid@.iscar.co.il> wrote in message news:uahTBFQQGHA.140@.TK2M
SFTNGP12.phx.gbl...|||Hi ,Dan
Yes , I missed 'a' , bit I think the OP got the idea:-)))
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:ed6hgSQQGHA.2496@.TK2MSFTNGP11.phx.gbl...
> If both SELECTs return the same value, you will lose one of them using
> UNION instead of UNION ALL as a duplicate will be discarded.
> BTW Uri, you appear to have mistyped the column name for the outer SUM,
> your query will error due to it trying to sum column bblala when the only
> column is blbla.
> SELECT SUM(blabla)
> FROM
> (
> select sum(ammount) as blabla from claimfinancialpayment where claimid =
> 10 UNION ALL select sum(ammount) from claimpaymenthistory where claimid =
> 10 ) AS Der
>
> Dan
>
> Roji. wrote on Mon, 6 Mar 2006 15:23:42 +0530:
>
>|||Roji
No , you won't get an exected result as I understood the OP
try
USE northwind
select sum(ord)
from
(
select sum(orderid) ord from orders
union --all
select sum(orderid) from orders
) as d
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:%23Hk5POQQGHA.5400@.TK2MSFTNGP09.phx.gbl...
> You should use UNION instead of UNION ALL there.
> --
> Regards
> Roji. P. Thomas
> http://toponewithties.blogspot.com
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uahTBFQQGHA.140@.TK2MSFTNGP12.phx.gbl...
>|||Sorry my mistake.
Infact you should use UNION ALL instead of UN ION :)
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eRIcksQQGHA.5116@.TK2MSFTNGP10.phx.gbl...
> Roji
> No , you won't get an exected result as I understood the OP
> try
> USE northwind
> select sum(ord)
> from
> (
> select sum(orderid) ord from orders
> union --all
> select sum(orderid) from orders
> ) as d
>
>
> "Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
> news:%23Hk5POQQGHA.5400@.TK2MSFTNGP09.phx.gbl...
>|||Just for variety, another alternative.
SELECT GrandTotal =
(select sum(ammount) from claimfinancialpayment
where claimid = 10) +
(select sum(ammount) from claimpaymenthistory
where claimid = 10)
Roy Harvey
Beacon Falls, CT
On Mon, 6 Mar 2006 11:38:44 +0200, "Robert Bravery" <me@.u.com> wrote:

>Hi all,
>if I have
>select sum(ammount) from claimfinancialpayment where claimid = 10
>select sum(ammount) from claimpaymenthistory where claimid = 10
>How can I add the two summed values together, when
>select sum(ammount) from claimfinancialpayment where claimid = 10
>sums three rows,
>and
>select sum(ammount) from claimpaymenthistory where claimid = 10
>sums 5 rows
>I was thinking of
>declare @.tot numeric(12,2), @.sum1 numeric(12,2), @.sum2 numeric(12,2)
>select @.sum1 = sum(ammount) from claimfinancialpayment where claimid = 10
>select @.sum2 = sum(ammount) from claimpaymenthistory where claimid = 10
>set @.tot = @.sum1+@.sum2
>Would this be correct or is there an easier or faster way of doing this
>THanks
>RObert
>|||THanks Uri, just what I needed
RObert
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uahTBFQQGHA.140@.TK2MSFTNGP12.phx.gbl...
> Robert
> SELECT SUM(bblala)
> FROM
> (
> select sum(ammount) as blbla from claimfinancialpayment where claimid = 10
> UNION ALL
> select sum(ammount) from claimpaymenthistory where claimid = 10
> ) AS Der
>
>
> "Robert Bravery" <me@.u.com> wrote in message
> news:OWgV$BQQGHA.4680@.TK2MSFTNGP10.phx.gbl...
10
>sql