На клиенте формируется кросс-таб, запрос естественно тоже формируется на клиенте. Мне нужны суммы по услугам и итоговая колонка в конце с общей суммой.
Вот фрагмент сгенерированного на клиенте запроса:
Код: Выделить всё
select a.abon_name, a.id as abon_id, c.id as contr_id, c.contract_no, c.contract_date ,
(select sum(pp.pay_sum) from payments pp where ((pp.contract_id = c.id) and (pp.service_id = 1) and (pp.pay_date between :date_start and :date_end))) as sum_1,
(select sum(pp.pay_sum) from payments pp where ((pp.contract_id = c.id) and (pp.service_id = 2) and (pp.pay_date between :date_start and :date_end))) as sum_2,
(select sum(pp.pay_sum) from payments pp where ((pp.contract_id = c.id) and (pp.service_id = 3) and (pp.pay_date between :date_start and :date_end))) as sum_3,
(select sum(pp.pay_sum) from payments pp where ((pp.contract_id = c.id) and (pp.service_id = 4) and (pp.pay_date between :date_start and :date_end))) as sum_4,
(select sum(pp.pay_sum) from payments pp where ((pp.contract_id = c.id) and (pp.service_id = 5) and (pp.pay_date between :date_start and :date_end))) as sum_5,
(select sum(pp.pay_sum) from payments pp where ((pp.contract_id = c.id) and (pp.service_id = 22) and (pp.pay_date between :date_start and :date_end))) as sum_22,
(select sum(pp.pay_sum) from payments pp where ((pp.contract_id = c.id) and (pp.service_id = 24) and (pp.pay_date between :date_start and :date_end))) as sum_24,
sum_1+sum_2+sum_3+sum_4+sum_5+sum_22+sum_24 as ITOG
from .....
Это я что-то не так сделал или FB 2.1 так не умеет?