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 datebut this query does not work