So I have a continuation of this question. New requirements have thrown in a third table.
So here's the breakdown of how the tables relate:
TabA(TabA_id, desc) TabB(TabB_id, TabA_id) TabC(TabC_id, TabA_id)
I need a count of each time a TabA desc occurs in both TabB and TabC, and I only need the records from TabC that have a TabA_id.
This is the query that is getting me close (sort of):
select COALESCE(count(b.TabC_id) + count(c.TabC_id), 0) as "Number of Records", a.desc as "Type", to_char(round(ratio_to_report(count(b.TabC_id) + count(c.TabC_id)) over()*100)) || '%' as "Percentage of Total" from TabA a left join TabB on a.TabA_id = TabB.TabA_id, TabB b left join TabC on b.TabC id = TabC.TabC_id, TabC c right join TabA on c.TabA_id = TabA.TabA_id where c.TabA_id is not null group by b.TabC_id, c.TabC_id, a.desc order by a.desc asc;
Hope this makes some sense. Looking forward to comments for clarification.