Calculate percentage of subqueries from the same table with group by and having clauses

Question

I'm using PostGres 10.12 DB which contains various fields about tests:

|test_name|result |report_time|main_version|environment|
|    A    |error  |29/11/2020 |     1      |   john    |
|    A    |failure|28/12/2020 |     1      |   john    |
|    A    |error  |29/12/2020 |     1      |   alice   |
|    B    |passed |30/12/2020 |     2      |   ben     |
|    C    |failure|31/12/2020 |     2      |   alice   |
|    A    |error  |31/12/2020 |     2      |   john    |

I'm trying to calculate the percentage of tests which have both 'failure/error' and 'passed' results out of all the tests that ran on the same day.

I created the following query:

SELECT s.environment, COUNT(*) AS total, COUNT(*)::float / t.total_tests * 100 as percentage
FROM (
     SELECT test_name, environment
     FROM tests where report_time >= now() - interval '5 day' 
     and main_version='1' and environment='John'
     GROUP BY test_name, environment
     having COUNT(case when result in ('failure', 'error') then 1 else null end) > 0 
     and count(case when result = 'passed' then 1 else null end) > 0
     order by environment asc
) s
CROSS JOIN (
      SELECT COUNT(*) AS total_tests FROM tests where report_time >= now() - interval '5 day' 
      and main_version='1' and environment='John'
) t
GROUP BY s.environment, t.total_tests  

Which works fine for a single environment and version. When I try to combine environments, the count is wrong.
How can I correctly calculate the correct percentage per day?

Answer 1

I'm trying to calculate the percentage of tests which have both 'failure/error' and 'passed' results out of all the tests that ran on the same day.

I don't know what "same day" is referring to. The sample data is taking data from a five day range, so I might guess that is what you mean.

In any case, the basic idea is to use conditional aggregation:

SELECT test_name, environment,
       AVG( (result = 'passed')::int ) as passed_ratio,
       AVG( (result in ('failure', 'error') )::int ) as fail_error_ratio
FROM tests 
WHERE report_time >= now() - interval '5 day'  AND
      main_version = '1' AND
      environment = 'John'
GROUP BY test_name, environment;

This returns ratios between 0 and 1. If you want percentages between 0 and 100 just multiply by 100.