Maps - 1 year ago 61

SQL 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 Source

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;
```