Percentage in a single SQL query

Question

Currently, I have two almost identical SQL queries that I use against the SQLITE3 database.

Find the total number of matching entries.

SELECT COUNT(runner.id)
FROM entry
JOIN runner ON runner.entry_id = entry.id
JOIN race ON race.id = entry.race_id
JOIN horse ON horse.id = entry.horse_id
WHERE STRFTIME('%Y-%m-%d', race.datetime) < :max_date AND
STRFTIME('%Y-%m-%d', race.datetime) >= :min_date AND
entry.trainer_id = :trainer_id AND
race.course_id = :course_id AND
race.type = :race_type

Find the total number of matching records, where x = y.

SELECT COUNT(runner.id)
FROM entry
JOIN runner ON runner.entry_id = entry.id
JOIN race ON race.id = entry.race_id
JOIN horse ON horse.id = entry.horse_id
WHERE STRFTIME('%Y-%m-%d', race.datetime) < :max_date AND
STRFTIME('%Y-%m-%d', race.datetime) >= :min_date AND
entry.trainer_id = :trainer_id AND
race.course_id = :course_id AND
runner.position = 1 AND
race.type = :race_type

Then I calculate the percentage value for x = y entries in the PHP code. How would I do the same to get a percentage of a single SQL query?

EDIT: Regarding optimization, I just switched the WHERE clauses to a more normal order and added some indexes, and it dropped from 17 seconds to 0.1 seconds.

Answer 1

Run your first query and change it:

SUM(CASE WHEN runner.position = 1 THEN 1 ELSE 0 END) as position1Count, COUNT(runner.id) as total

Then divide the postionCount by the total number in PHP.