jeudi 5 mars 2015

Adding counts inside a COALESCE


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.





Aucun commentaire:

Enregistrer un commentaire