Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Query case
Message
 
 
To
17/03/2017 07:30:00
General information
Forum:
Visual FoxPro
Category:
Coding, syntax & commands
Title:
Environment versions
Visual FoxPro:
VFP 9
OS:
Windows 7
Network:
Windows XP
Database:
MS SQL Server
Application:
Desktop
Miscellaneous
Thread ID:
01649116
Message ID:
01649117
Views:
52
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
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform