How to write average innerQuery

Question

From the Image I have same course id and multiple videos for i want to show the overall watched percentage as the average of them how can I do that I want something like this:

(SELECT SUM(watched_percentage) FROM tbl_student_learning_path where course_id = 298 
AND SELECT COUNT(watched_percentage) FROM tbl_student_learning_path where course_id = 298)
as overallScore

Normally, the overall watched percentage would be `(SUM(watched_time) / SUM(total_time)) * 100`, or `((45+5)/(93+5)) * 100` or 51.02%, not the average of 48% and 100% or 74%. The latter is almost totally meaningless as a figure. — Jonathan Leffler, May 09 '17 at 06:06
Absolutely agree with @JonathanLeffler, and was going to write something similar as an answer but didn't have time. And now I see a much inferior answer has been accepted!? — Steve Lovell, May 09 '17 at 06:27

score 2 · Accepted Answer · answered May 09 '17 at 07:33

2

As per comments, you'd get a weighted average this way

SELECT
    course_id,
    (100 * SUM(watched_total_time) / SUM(video_total_time)) AS WeightedAvg
FROM
    tbl_student_learning_path 
WHERE
    course_id=298
GROUP BY
    course_id

answered May 09 '17 at 07:33

Steve Lovell

2,564
2
13
16

score 1 · Answer 2 · answered May 09 '17 at 05:09

1

SELECT course_id,AVG(watched_percentage) AS Avg
FROM tbl_student_learning_path 
WHERE course_id=298
GROUP BY course_id

answered May 09 '17 at 05:09

Jibin Balachandran

3,381
1
24
38

How to write average innerQuery

2 Answers2