1

I am attempting to build a report for a client. I need a display of all the values in Tab1, Col1 (a validation table), a count of each time that occurred, and a percentage of the total. Here's what I'm looking for:

Number of records       Type        Percentage of total
2                       A           2
3                       B           3
0                       C           0

I have the basic query worked out, I just don't know how to include the zero count! Here's what I have so far:

select count(b.Col1) "Number of Records",
a.col2 "Type",
to_char(round(ratio_to_report(count(a.Col1)) over()*100)) || '%' as "Percantage of Total"
from Tab1 a,
Tab2 b
where Tab2.Col1 = Tab1.Col1(+)
group by a.Col1;

Thanks in advance.

EDIT: Attempting this:

select count(b.Col1) "Number of Records", 
a.col2 "Type",
to_char(round(ratio_to_report(count(b.Col1)) over()*100)) || '%' as "Percantage of Total"
from Tab2 b
left join Tab1 a on tab2.col1 = tab1.col1 -- or (from tab2 left join tab1) if that is what's intended
group by a.Col1;

Gave me the same results as my query.

This:

select COALESCE(count(b.Col1), 0) AS "Number of Records", 
a.col2 "Type",
to_char(round(ratio_to_report(count(b.Col1)) over()*100)) || '%' as "Percantage of Total"
from Tab1 a
left join Tab2 b on tab1.col1 = tab2.col1
group by a.Col1;

Also gives me the same results.

EDIT 2:

Table A columns: ID, Code, Description

Table B Columns: ID, RecordNumber, TableA_ID

Table B is a "master" and Table A is one of the many "details". Basically I need a count of TableA instances in TableB.. including the zeros.

Query that gave me my desired results:

select coalesce(count(b.TabA_id),0) as "num records",
a.desc "type",
to_char(round(ratio_to_report(count(b.TabA_id)) over()*100)) || '%' as "Percentage of Total"
from TabB b
right join TabA a on b.TabA_id = a.id
group by  b.TabA_id, a.id
order by a.desc;
  • 1
    You'll want to change both count() records to table b... – Joishi Bodio Feb 17 '15 at 21:41
  • Oh my, I haven't seen the ancient syntax (+) for a long time. You should really use the "new" outer join syntax ("new" as in 23 years new.) – ypercubeᵀᴹ Feb 17 '15 at 21:43
  • @ypercube Yeah, I know. Just trying to keep the queries looking the same in our reports. – Brian Robbins Feb 17 '15 at 21:45
  • For more assistance you'll need to provide us with the table definitions for tab1 and tab2.. – Joishi Bodio Feb 17 '15 at 21:59
2

There's not much information you provide, so this is only a guess.

Your WHERE clause Tab2.Col1 = Tab1.Col1(+) may be backward, with regard to the (+) ... I would recommend switching it to a LEFT JOIN regardless, just to make it more readable..

select
  count(b.Col1) "Number of Records", --- CHANGED a.Col1 to b.Col1
  a.col2 "Type",
  to_char(round(ratio_to_report(count(b.Col1)) over()*100)) || '%' as "Percantage of Total"
from Tab1 a
left join Tab2 b on tab1.col1 = tab2.col1 -- or (from tab2 left join tab1) if that is what's intended
group by a.Col1;

If you are still not getting the 0 counts, then you should try a coalesce statement.

select
  COALESCE(count(b.Col1), 0) AS "Number of Records", --- COALESCE HERE .. CHANGED a.Col1 to b.Col1
  a.col2 "Type",
  to_char(round(ratio_to_report(count(b.Col1)) over()*100)) || '%' as "Percantage of Total"
from Tab1 a
left join Tab2 b on tab1.col1 = tab2.col1
group by a.Col1;

You are not referencing table b anywhere in your sql, though - please explain why you need to join to it if you're not going to be using its data?

  • table a is a validation table where I get the list. Table b is the actual number of records. Should I be counting table b instead of table a? – Brian Robbins Feb 17 '15 at 21:28
  • Yes. Counting a instead of b may sometimes be the same .. but it will not work if b is NULL (which in this case it will be when you SHOULD have a 0) My first SQL should do that. Assuming I understand what you mean correctly. – Joishi Bodio Feb 17 '15 at 21:31
  • And it was a right join I needed to get the results. Maybe I had the join condition in the wrong order... but yay for guess and check. – Brian Robbins Feb 17 '15 at 22:19
  • 1
    You need either tab1 left join tab2 or tab2 right join tab1. Most people prefer to use only left joins. – ypercubeᵀᴹ Feb 17 '15 at 22:23

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.