Dear Experts
I have this query
select data1.acc_code,
SUM(case type when 1 then qty else 0 end )as op_qty,
SUM(case type when 2 then qty else 0 end )as pr_qty,
SUM(case type when 3 then qty else 0 end )as sl_qty,
SUM(case type when 4 then qty else 0 end )as sr_qty,
SUM(op_qty)+SUM(pr_qty)as total_qty
from
(select acc_code,qty, 1 as type from master where SUBSTRING(acc_code,1,2)='32' and LEN(acc_code)=7
union all
select acc_code, usedd as qty,2 as type from product where SUBSTRING(acc_code,1,2)='32' and LEN(acc_code)=7
union all
select code as acc_code,qty, 3 as type from cashsalp where SUBSTRING(code,1,2)='32' and LEN(acc_code)=7
union all
select acc_code, qty,3 as type from crsalp where SUBSTRING(acc_code,1,2)='32' and LEN(acc_code)=7
union all
select code as acc_code, qty, 4 as type from srsalp where SUBSTRING(code,1,2)='32' and LEN(acc_code)=7)
as data1
group by acc_code
order by acc_code
At Line 6, I am trying to get sum of more than one fields as
SUM(op_qty)+SUM(pr_qty) as total_qty
But it says:
Msg 207, Level 16, State 1, Line 6
Invalid column name 'open_qty'.
Please help