Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Sum fields in query
Message
 
 
To
15/11/2016 03:24:29
General information
Forum:
Visual FoxPro
Category:
Databases,Tables, Views, Indexing and SQL syntax
Environment versions
Visual FoxPro:
VFP 9
OS:
Windows 7
Network:
Windows XP
Database:
MS SQL Server
Application:
Desktop
Miscellaneous
Thread ID:
01643456
Message ID:
01643459
Views:
41
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
If it's not broken, fix it until it is.


My Blog
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform