views:

117

answers:

4

From the 1st query I am getting some value and from 2nd query I am getting some value. I want the sum of the two values.

Query 1:

select sum(EAmount) from EstimateAmount where pid='3' group by pid

Query 2:

select sum(OPEAmount) from OPEAmount where pid='3' group by pid
+10  A: 
select 

(select sum(EAmount) from EstimateAmount 
where pid='3' 
group by pid) 
+ 
(select sum(OPEAmount) from OPEAmount 
where pid='3' 
group by pid)
Mitch Wheat
+2  A: 

Mitch solution is correct, I just want to add a more generic one for the cases when you need the sum for all pids and that can be extended to more aggregates:

with agg_EA as (
    select pid, sum(EAmount) as sumEA
    from EstimateAmount 
    group by pid) 
, agg_OPEA as (
    select pid, sum(OPEAmount) as sumOPE
    from OPEAmount 
    group by pid)
select sumEA+sumOPE
    from agg_EA
    join agg_OPEA on agg_EA.pid = agg_OPE.pid
Remus Rusanu
A: 

You can also use Union All and within a nested table when aggregating sums of sums

select sum(agg.Total) as GrandTotal
from ( select sum(EAmount) as Total from EstimateAmount where pid='3' group by pid
   union all
   select sum(OPEAmount) as Total from OPEAmount where pid='3' group by pid
 ) agg
Sung Meister
A: 

Just join them:

SELECT sum(coalesce(e.EAmount,0) + coalesce(o.OPEAmount,0))
FROM EstimateAmount e
LEFT JOIN OPEAmount o ON o.pid = e.pid
WHERE e.pid = 3
GROUP BY e.pid
MDStephens