Thanks my professor,

Now it has problem on this line

select code as acc_code,  0 as op_qty, 0 as pr_qty, sum(qty) as sl_qty, 0 as sr_qty from cashsalp  where  code like '32%' and LEN(acc_code)=7
       GROUP BY acc_code

Msg 8120, Level 16, State 1, Line 10
Column 'cashsalp.code' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Please help again





>Try
>
>

>
>select data1.acc_code,
>op_qty,
>pr_qty,
> sl_qty,
> sr_qty,
> op_qty + pr_qty as total_qty
>	from
>	(select acc_code, sum(qty) as op_qty, 0 as pr_qty, 0 as sl_qty, 0 as sr_qty from master where acc_code LIKE '32%' and LEN(acc_code)=7
>        GROUP BY acc_code
>	union all
>	select acc_code, 0 as op_qty, sum(usedd) as pr_qty, 0 as sl_qty, 0 as sr_qty  from product  where acc_code like '32%'  and LEN(acc_code)=7
>       GROUP BY acc_code
>	union all
>    select code as acc_code,  0 as op_qty, 0 as pr_qty, sum(qty) as sl_qty, 0 as sr_qty from cashsalp  where  code like '32%' and LEN(acc_code)=7
>       GROUP BY acc_code
>	union all
>	select  acc_code, 0 as op_qty, 0 as pr_qty, sum(qty) as sl_qty, 0 as sr_qty from crsalp  where acc_code like '32%' and LEN(acc_code)=7
>        group by acc_code
>	union all
>	select code as acc_code, 0 as op_qty, 0 as pr_qty, 0 as sl_qty, sum(qty) as sr_qty from srsalp  where code like '32%' and LEN(acc_code)=7
>	group by acc_code)	
>	as data1
>	
>	order by acc_code
>

>
>>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