CODEWITHSUNDEEP
sqlsql-server-2008-r2
Saaharjit Singh
Saaharjit Singh

Reputation: 125

getting wrong values while joining tables?

MY tables are

Parent WO

WO     PRICE
1      1790    
1      9

Child WO

WO     PRICE
1      200    
1      400    
1      600
1      100

I am trying to do this

Select  sum(p.price), SUM(c.price) from Parent_WO p
left outer join Child_WO c
on p.WO= c.WO  
group by p.WO

I am getting the wrong value for the p.price. I am getting 7196 when it should by 1796. It's multipe the total of the parent WO with the 4 child WO.

Upvotes: 0

Views: 34

Answers (2)

Valerica
Valerica

Reputation: 1630

Try using 2 CTE's; calculate Parent price in one CTE and Child price in another CTE and join the results:

with parent_sum as (
  select
    wo
    ,SUM(price) as ParentSUM
  from parent_wo
  group by wo
)
,child_sum as (
  select
    wo
    ,SUM(price) as ChildSUM
  from child_wo
  group by wo
)
select
  p.wo
  ,ParentSUM
  ,ChildSUM
from parent_sum p
left join child_sum c
  on p.wo = c.wo

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Do the aggregation before doing the sum():

Select coalesce(p.wo, c.wo) as wo, sum(p.price), SUM(c.price)
from (select p.wo, sum(p.price) as parent_price
      from Parent_WO p
      group by p.wo
     ) p full outer join
     (select c.wo, sum(c.price) as child_price
      from Child_WO c
      group by c.wo
     ) c
     on p.WO = c.WO ;

Upvotes: 0

Related Questions