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;
(+)
for a long time. You should really use the "new" outerjoin
syntax ("new" as in 23 years new.) – ypercubeᵀᴹ Feb 17 '15 at 21:43