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