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.

  • Not sure I understand your question, do you need a count or the records (rows) from tabC? – Lennart Mar 5 '15 at 18:07
  • Sorry, the alias is a bit misleading. I need a count from tabC and tabB any time an instance of tabA occurs. – Brian Robbins Mar 5 '15 at 18:18

I'm giving it a shot, still not sure if I got it:

select 100.0*(count(b.taba_id) + count(c.taba_id)) 
     / (( select count(1) from b) + (select count(1) from c)) 
from taba as a
left join tabb as b 
    on a.id = b.id 
left join tabc as c
    on a.id = c.id

Some thoughts, count will newer return null so coalesce is pointless. If the above is correct it is possible to use window function (as I think you have tried to do), to replace the total count's

  • Yeah, I just found out about the count piece. I was (most likely) restricting my original query too far and not getting them. I'll check this out, thanks! – Brian Robbins Mar 5 '15 at 19:24
  • You were leading me in the right direction, but not quite there. – Brian Robbins Mar 5 '15 at 19:44

Here's what wound up working for me:

    count(union_all.TabA_id) as "Count of A",
    to_char(round(ratio_to_report(count(union_all.TabA_id)) over()*100))
      || '%' as "Percentage of Total"
    TabA a
  left join 
      select TabA_id from TabC
      union all
      select TabA_id from TabB
      on union_all.TabA_id = a.TabA_id
group by 
order by 

Thanks @Lennart for the help!

Edited per @ypercube's keen eye.

  • Indeed, you are correct. Caught another one too.. – Brian Robbins Mar 5 '15 at 20:49
  • 1
    Took the liberty of reformatting (and converting that right join to left). I hope you don't mind. – ypercubeᵀᴹ Mar 5 '15 at 20:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.