How to add values ​​coming from 2 queries

From query 1 st I get some value and from query < nd I get some value. I want to get the sum of two values.

Request 1:

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

Request 2:

select sum(OPEAmount) from OPEAmount where pid='3' group by pid
+3
source share
4 answers
select 

(select sum(EAmount) from EstimateAmount 
where pid='3' 
group by pid) 
+ 
(select sum(OPEAmount) from OPEAmount 
where pid='3' 
group by pid) 
+13
source

Mitch's solution is correct, I just want to add a more general option for cases when you need an amount for all pids and which can be expanded 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
+3
source

Union All

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
0

:

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
0

Source: https://habr.com/ru/post/1710126/


All Articles