Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
Sum fields in query
Message
De
15/11/2016 03:24:29
 
 
À
Tous
Information générale
Forum:
Visual FoxPro
Catégorie:
Base de données, Tables, Vues, Index et syntaxe SQL
Titre:
Sum fields in query
Versions des environnements
Visual FoxPro:
VFP 9
OS:
Windows 7
Network:
Windows XP
Database:
MS SQL Server
Application:
Desktop
Divers
Thread ID:
01643456
Message ID:
01643456
Vues:
56
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
Suivant
Répondre
Fil
Voir

Click here to load this message in the networking platform