vendredi 27 février 2015

Using column short-names defined previously in the same SQL statement

I'm making some statistics from a table having values for expected and actual results.


this statement works perfectly:



select model_ref AS [Model],
count(id) AS Tested,
count(case when expected_result = predicted_result then predicted_result end) AS OK,
count(case when NOT(expected_result = predicted_result) then expected_result end) AS Wrong,
( (count(case when expected_result = predicted_result then predicted_result end)*1.0) / count(id) * 100 ) AS [Accuracy (%)]
from Results


but this not:



select model_ref AS [Model],
count(id) AS Tested,
count(case when expected_result = predicted_result then predicted_result end) AS OK,
count(case when NOT(expected_result = predicted_result) then expected_result end) AS Wrong,
( OK *1.0) / Tested * 100 ) AS [Accuracy (%)]
from Results


The difference is only, I wanted to shorten the expression by using column names defined previously - Tested and OK


Is it possible?


P.S.: If it is of any importance, the table is:



CREATE TABLE Results(
id INTEGER PRIMARY KEY AUTOINCREMENT,
expected_result TEXT,
image_id TEXT,
model_ref TEXT,
predicted_result TEXT,
test_date TEXT)

Aucun commentaire:

Enregistrer un commentaire