Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
Query case
Message
 
 
À
17/03/2017 07:30:00
Information générale
Forum:
Visual FoxPro
Catégorie:
Codage, syntaxe et commandes
Titre:
Versions des environnements
Visual FoxPro:
VFP 9
OS:
Windows 7
Network:
Windows XP
Database:
MS SQL Server
Application:
Desktop
Divers
Thread ID:
01649116
Message ID:
01649117
Vues:
53
You need to move SUM outside the case, e.g.
select  [date], count(data.vou_no) as tot,
SUM(case when data.type=1  then data.vou_no end) as ac_cr,
SUM(case when data.type=2  then data.vou_no end) as ac_dr
	from (
	select count(vou_no)as vou_no,date, 1 as type  from vouchers where VOU_type='CR' and cr_amount>0  group by date
	union all
	select count(vou_no)as vou_no,date, 2 as type from vouchers where VOU_type='CP' and dr_amount>0 group by date
	) data
	group by [date]
>Hello Experts
>
>I have this query
>
>
>select max(DATE)as date, count(data.vou_no) as tot,
>(case when data.type=1  then sum(data.vou_no) end) as ac_cr,
>(case when data.type=2  then sum(data.vou_no) end) as ac_dr
>	from (
>	select count(vou_no)as vou_no,date, 1 as type  from vouchers where VOU_type='CR' and cr_amount>0  group by date
>	union all
>	select count(vou_no)as vou_no,date, 2 as type from vouchers where VOU_type='CP' and dr_amount>0 group by date
>	) data
>	group by date
>
>
>but this query does not work
>it says:
>Msg 8120, Level 16, State 1, Line 3
>Column 'data.type' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
>
>as shown in image
>
>How to overcome this?
>
>Please help
If it's not broken, fix it until it is.


My Blog
Précédent
Suivant
Répondre
Fil
Voir

Click here to load this message in the networking platform