SQL query to count total ocurrences of values in one column and relative occurrence in another column

Question

This is my first post, so any general corrections to format/content are also welcome. I'm relatively new to SQL.

Say I have a database which collects test results from an classification evaluation. I know what the expected outcome is for each test. I also have a column indicating whether the test was successful, ie the expected value returned matched the expected value. It looks something like this:

Expected_Result   Result    Success

   A                  A     True

   A                  B     False

   B                  B     True

   A                  A     True

   B                  A     False

I know I can return the total occurrences of each expected type with

SELECT Expected_Result, COUNT(Expected_Result) FROM Evaluation_Results GROUP BY Expected_Result

.
I know how to count the number of false detections for a specific expected outcome with

SELECT COUNT(*) FROM Evaluation_Results WHERE Success = 'True' AND Expected_Result = 'A'

Where I'm struggling is combining the two. I would like the query to return a list of all distinct expected results, the total of each, the count of successful results, and the percentage of the total, like so:

Expected_Result     Total   Num_Successful  Success_Rate

A                    3          2             66.67

B                    2          1             50.00

Answer 1

You could use a CASE expression to perform a condition check during aggregation. A case statement identifies a conditional outcome. For instance you could use:

select evaluation_result
, count(*) AS total
, sum(case when success='true' and result='a' then 1 else 0 end) AS num_successful
, sum(case when success='true' and result='a' then 1 else 0 end)/count(*) AS success_rate
from evaluation_results group by evaluation_result;

Basically what's happening there is you're taking a count(*) of all grades, a sum() of a 1 or 0 based on a conditional outcome, then performing the ratio math. There's no need for a join here. The CASE Expression is a powerful conditional statement which can be used in so many diverse ways.

Or for a more flexible solution have a look at this:

select evaluation_result
, count(*) AS total
, sum(case when success='true' and result=evaluation_result then 1 else 0 end) AS num_successful
, sum(case when success='true' and result=evaluation_result then 1 else 0 end)/count(*) AS success_rate
from evaluation_results group by evaluation_result;

SQL query to count total ocurrences of values in one column and relative occurrence in another column

Sign up