Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Sum fields in query
Message
From
15/11/2016 03:24:29
 
 
To
All
General information
Forum:
Visual FoxPro
Category:
Databases,Tables, Views, Indexing and SQL syntax
Title:
Sum fields in query
Environment versions
Visual FoxPro:
VFP 9
OS:
Windows 7
Network:
Windows XP
Database:
MS SQL Server
Application:
Desktop
Miscellaneous
Thread ID:
01643456
Message ID:
01643456
Views:
55
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
Next
Reply
Map
View

Click here to load this message in the networking platform