>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
Thanks it works now