oracle - Use of AVG function to determine percentages in a SQL query -

oracle - Use of AVG function to determine percentages in a SQL query -

i want know percentage of records have given value, percentage defined number of records match value divided total number of records. i.e. if there 100 records, of 10 have null value student_id , 20 have value of 999999, percentage_999999 should 20%. can utilize avg function determine this?

option 1:

select year, college_name, sum(case when student_id in ('999999999') 1 else 0 end) count_id_999999999, count_id_999999999/total_id percent_id_999999999, sum(case when student_id null 1 else 0 end) count_id_null, count_id_null/total_id percent_id_null count(*) total_id enrolment_data ed grouping year, college_name order year, college_name;

option 2:

select year, college_name, sum(case when student_id in ('999999999') 1 else 0 end) count_id_999999999, avg(case when student_id in ('999999999') 1.0 else 0 end) percent_id_999999999, sum(case when student_id null 1 else 0 end) count_id_null, avg(case when student_id null 1.0 else 0 end) percent_id_null count(*) total_id enrolment_data ed grouping year, college_name order year, college_name;

i created similar table 100 records, 20 999999999s, 10 nulls, , 70 1s. worked me on sql server:

select count(*), studentid scratchtbl grouping studentid; (no column name) studentid 10 null 70 1 20 999999999 select avg(case when studentid = '999999999' 1.0 else 0.0 end) 'pct_9s', sum(case when studentid = '999999999' 1 else 0 end) 'count_9s', avg(case when studentid null 1.0 else 0.0 end) 'pct_null', sum(case when studentid null 1 else 0 end) 'count_null' scratchtbl pct_9s count_9s pct_null count_null 0.200000 20 0.100000 10

i have feeling utilize of grouping clause creating problems you, perhaps select specific year/college using clause (and rid of grouping line) , see if results expect.

sql oracle