I am trying to create a query which returns the workout percentage completion…
Workout Percentage Completion =
((Sum of LogEntries WHERE date = IN list of dates and WorkoutID =1 ) /
(Sum of all set numbers WHERE WorkoutID = 1)) x 100
Below is what I currently have, at the moment it is only returning the result of the first query. What should I change for the query to run correctly?
x
SELECT
(
(
SELECT COUNT(LogEntriesID)
FROM LogEntriesTable
LEFT JOIN ExerciseWorkoutJunctionTable
ON ExerciseWorkoutJunctionTable.ExerciseWorkoutJunctionID =
LogEntriesTable.JunctionID
WHERE LogEntriesTable.date IN (
"14-05-2020", "15-05-2020", "16-05-2020", "17-05-2020",
"18-05-2020", "19-05-2020", "20-05-2020"
)
AND ExerciseWorkoutJunctionTable.WorkoutID = 1
) / (
SELECT sum(SetNumber)
FROM ExerciseWorkoutGoalsTable
LEFT JOIN ExerciseWorkoutJunctionTable
ON ExerciseWorkoutJunctionTable.ExerciseWorkoutJunctionID =
ExerciseWorkoutGoalsTable.JunctionID
WHERE ExerciseWorkoutJunctionTable.WorkoutID = 1
)
)
Advertisement
Answer
Your first SELECT statement is doing an OUTER JOIN but then you have a WHERE clause that is selecting non-NULL values from the ExerciseWorkoutJunctionTable table, so I suspect you might as well be doing an INNER JOIN.
When you have two queries, try:
SET @sum = (SELECT SUM(SetNumber) etc .);
SELECT (COUNT(LogEntriesID) * 100 / @sum) AS percentage
FROM etc.