2表联合查询的合计有关问题

2表联合查询的合计问题
a是入库表 
b是出库表 

c是产品基本表 

某产品x入库一次10件,出库2次每次10件共20件 

想用一条语句查询出入库合计和出库合计,用以下语句,但有错误: 

select 
  c.Part_Num, 
  sum(a.InCount) as inCount, 
  sum(b.OutCount) as outCount 
from c 
left join a on c.Part_Num=a.Part_Num 
left join b on b.Part_Num=a.Part_Num 
group by c.Part_Num 

正确结果入库合计10件,出库合计20件 

但用以上语句显示结果为入库合计20件,出库合计20件. 

a表 
part_Num inCount 
x 10 

b表 
part_Num outCount 
x 10 
x 10 

想要的结果是在一条记录中得到入库合计和出库合计。 

part_Num sum(inCount) sum(outCount) 
x 10 20

谢谢

[解决办法]

SQL code

select  c.Part_Num,  a.InCount as inCount,  b.OutCount as outCountfrom cleft join ((select part_Num,sum(inCount) as inCount from a group by part_Num)) as a on c.Part_Num=a.Part_Numleft join (select part_Num,sum(outCount) as outCount from b group by part_Num) as b on b.Part_Num=a.Part_Numgroup by c.Part_Num
[解决办法]
SQL code
select Part_Num,sum(InCount),sum(OutCount)from (select Part_Num,InCount,0 as OutCount from aunion allselect Part_Num,0 as InCount,OutCount from bunion allselect Part_Num,0,0 from c)group by Part_Num