Thread: sum of two queries

Is there a way to return the sum of two separate queries as in:

select (select sum(price) from items) + (select sum(price) from
widgets))

select 3 + 4 works OK but the parser doesn't like sticking a subquery in
place
of the numbers.

Is there another way to do this?

kyle@actarg.com

G. Anthony Reina wrote: <blockquote type="CITE">Kyle Bateman wrote: <p>> Is there a way to return the sum of two
separatequeries as in: <br />> <br />> select (select sum(price) from items) + (select sum(price) from <br />>
widgets))<br />> <br />> select 3 + 4 works OK but the parser doesn't like sticking a subquery in <br />>
place<br />> of the numbers. <br />> <br />> Is there another way to do this? <br />> <br />>
kyle@actarg.com<p>Try : <p>select sum(a.price + b.price) from items as a, widgets as b; <p>-Tony Reina</blockquote> I
explainedmy problem badly. <p>Here's the full thing.  I'm doing this, which works: <p><tt>select -sum(tquant) from
mtr_regwhere</tt><br /><tt>    status = 'clsd' and</tt><br /><tt>    fr_proj = 20 and</tt><br /><tt>    pnum =
'1122'</tt><br/><tt>union</tt><br /><tt>select sum(tquant) from mtr_reg where</tt><br /><tt>    status = 'clsd'
and</tt><br/><tt>    to_proj = 20 and</tt><br /><tt>    pnum = '1122'</tt><br /><tt>;</tt><br />This yields two
numbers,one negative and the other positive.  If I add them together <br />in the application, I get the number I
reallywant which represents the total number of <br />part 1122 that have come into inventory (project 20) minus the
totalnumber that have gone out <br />(i.e. current stock level). <p>What I'm trying to do is to have SQL do the
additionrather than having to read the <br />two sums separately and add them externally. <p>I think the example you
gaveadds up all the prices of all the combinations of pairs from <br />aliases a and b (billions and billions...). <br
/><tt></tt> <pre>-- 
 
 ----------------------------------------------------
 Kyle Bateman       President, Action Target Inc.
 "Viva Yo!"         kyle@actarg.com (801)377-8033x101
 ----------------------------------------------------</pre>  

Kyle Bateman wrote:

>>
>
> I explained my problem badly.
>
> Here's the full thing.  I'm doing this, which works:
>
> select -sum(tquant) from mtr_reg where
>     status = 'clsd' and
>     fr_proj = 20 and
>     pnum = '1122'
> union
> select sum(tquant) from mtr_reg where
>     status = 'clsd' and
>     to_proj = 20 and
>     pnum = '1122'
> ;

I think this will work (it seems to work when I tried it with my
database):

select sum(a.tquant - b.tquant) from mtr_reg as a, mtr_reg as b where
a.status = 'clsd' and
b.status = 'clsd' and a.pnum = '1122' and b.pnum = '1122' and a.fr_proj
= 20 and b.to_proj = 20;


-Tony