Maps Maps - 5 months ago 14
SQL Question

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

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

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;