asked on
select sum(total) total, name, count(basecount) rowcount
from (
select sum(lsa.amount) total, bank_name name, count(l.id) basecount
from loans l join loan_sub_account lsa on l.id = lsa.loan_id
join products p on l.product_id = p.id
join banks b on p.bank_id=b.id
join members m on l.member_id=m.id
join originator o on m.orig_id=o.id
left outer join assoc_mem_group amg on lsa.assoc_mem_group_id=amg.id
where trunc(lsa.lodged_date) between trunc(TO_DATE('1-7-2007 10:48:12','DD-MM-YYYY HH24:MI:SS'))
and trunc(TO_DATE('11-7-2008 10:48:12','DD-MM-YYYY HH24:MI:SS'))
and l.member_id in
( SELECT m.member_id
from mv_all_group_orig_member m where m.group_id = 503 )
and ( b.panel_lender = 1 )
group by bank_name, l.id
having sum(lsa.amount) > 0
) base
group by name
order by name;