Avatar of bisjom

asked on 

Oracle sql count of count

Hi I need to get a count of count .
If I use the code attached, I will get the correct count.
But i used a subquery. I need to avoid that subquery and need to get the count( count ( basecount) which is the rowcount of that query.
how can i get it?
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;

Open in new window

Oracle DatabaseSQL

Avatar of undefined
Last Comment
Muhammad Kashif

8/22/2022 - Mon