average per row based on a boolean value count in postgres

Question

Is it possible to find the average of multiple rows in a column grouped by some criteria, but the average is not a numerics value, actually it another count under a specific boolean condition:

select count(commit) from table_x where contains_bug = 'True' group by project name_name;

This will return the number of risky commits per project.

select count(commit) from table_x group by project name_name;

This will return the total number of commits per project

I tried to find the average of risky commits per project by:

Select AVG(select count(commits)from table_x where contains_bug = 'True') group by project_name;

it did not work

Thanks

Answer 1

You can use AVG (but you need to CAST to INT first):

SELECT AVG(CAST(contains_bug = 'True' AS int)) FROM table_x GROUP BY project_name;

Answer 2

I got the answer finally :)

select project_name, to_char(count(nullif(contains_bug = 'True', false))/count(*)::float*100, '99.99') as percentage from table_x group by project_name;