Percentage in a single SQL query

Question

I currently have two almost identical SQL queries, which I use against my SQLITE3 database.

Find total count of matching records.

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 total count 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

I then calculate the percentage off of x = y records in PHP code. How would I do the same thing a return a percentage from a single SQL query?

EDIT: In regards to optimisation, I have just switched around the WHERE conditions into a more sane order, and added some indexes and it is down from 17 seconds to 0.1 seconds.

Answer 1

Run your first query and change the select to:

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 in PHP.