I am doing a select query where I select a row to edit and display the values - however, I wish my select value to get the average of the whole column.
The select is as follows:
The column is rating_learner and we are looking for an average of the rating_learner.total column
The join is where the user id matches the lesson creator (q)
avg( if (rating_learner.teacher_user_id=q.user_id,rating_learner.total,null) ) as average_rating
However, when I select where = lesson id and group by lesson id, I can no longer see the full average of the column - only that average of that unique record.
The objective is to see the average of the full column - despite only choosing one record.
would be a lot easier to diagnose with the full query, but⌠essentially there are two/three methods:
1: store an average in a separate table, (re)calculate it on entry, and pull it with your specific query on an outer join;
2: calculate the average on the fly and likewise join it to your specific query
3: execute two queries. Which is basically the same thing as 1 and 2, but with more overhead.
SELECT q.request_lesson_id , q.user_id , q.product_types , users.username as username , bids.user_id as bid_user_id , rating_learner_private_lessons.is_rated as is_rated , rating_learner_private_lessons.id as rating_id , ( SELECT avg(if(rating_learner_private_lessons.teacher_user_id=q.user_id,rating_learner_private_lessons.total,null)) FROM rating_learner_private_lessons ) as average_rating FROM ( SELECT request_lessons.id AS request_lesson_id , request_lessons.user_id AS user_id , GROUP_CONCAT(request_lesson_items.item ORDER BY request_lesson_items.item) AS product_types FROM request_lessons LEFT JOIN request_lesson_items ON request_lesson_items.request_lesson_id = request_lessons.id GROUP BY request_lessons.id ) AS q LEFT JOIN rating_learner_private_lessons ON rating_learner_private_lessons.request_lesson_id = q.request_lesson_id INNER JOIN bids ON bids.user_id = rating_learner_private_lessons.teacher_user_id INNER JOIN users ON users.id = rating_learner_private_lessons.teacher_user_id && rating_learner_private_lessons.request_lesson_id =2 GROUP BY rating_learner_private_lessons.request_lesson_id
it is a rather long query as it is pulling data from quite a number of tables. I am trying to do a sub query as follows:
( SELECT avg(if(rating_learner_private_lessons.teacher_user_id=q.user_id,rating_learner_private_lessons.total,null)) FROM rating_learner_private_lessons ) as average_rating
That query hurts my head. What exactly are you trying to get out of it? Youâre left joining a table which youâre then inner joining, and have a sub-query thrown in as wellâŚ
Are you trying to show all the lessons a teacher teaches, with a rating for those lessons if the teacher has any ratings for them?
Joined with items - here we put a sub list of subjects
Basically 1 and 2 is just 'q - you can pretty much ignore that stuff.
We need to find the teacher who is hired - joined with âBidâ (teacher who succesasfully bid)
Get the username of the teacher who bid found in users
The ratings table is done by the learner where they rate the teacher here the teacher_user_id is stored in the learnerâs rating table (it is owned by the learner - conceptually)
We need to get all of that data for one lesson - but get the average of the teachers ratings found in the learners tableâŚ
It all works until I try to get the average of the column relating to teacher_user_id from the ratings table.
I have been messing around with the following:
Really, I think that I just want to isolate this query:
( SELECT avg(if(rating_learner_private_lessons.teacher_user_id=q.user_id,rating_learner_private_lessons.total,null)) FROM rating_learner_private_lessons ) as average_rating
No worries if it is too complex without the full schema. I can muddle about until I get it.
itâs almost always better to show the actual query
here it is again, all iâve done is reformatted it for humans â
SELECT q.request_lesson_id
, q.user_id
, q.product_types
, users.username as username
, bids.user_id as bid_user_id
, rating_learner_private_lessons.is_rated as is_rated
, rating_learner_private_lessons.id as rating_id
, ( SELECT avg(if(rating_learner_private_lessons.teacher_user_id=q.user_id
,rating_learner_private_lessons.total
,null))
FROM rating_learner_private_lessons ) as average_rating
FROM ( SELECT request_lessons.id AS request_lesson_id
, request_lessons.user_id AS user_id
, GROUP_CONCAT(request_lesson_items.item
ORDER BY request_lesson_items.item) AS product_types
FROM request_lessons
LEFT
JOIN request_lesson_items
ON request_lesson_items.request_lesson_id = request_lessons.id
GROUP
BY request_lessons.id ) AS q
LEFT
JOIN rating_learner_private_lessons
ON rating_learner_private_lessons.request_lesson_id = q.request_lesson_id
INNER
JOIN bids
ON bids.user_id = rating_learner_private_lessons.teacher_user_id
INNER
JOIN users
ON users.id = rating_learner_private_lessons.teacher_user_id
&& rating_learner_private_lessons.request_lesson_id =2
GROUP
BY rating_learner_private_lessons.request_lesson_id
you say youâre trying to add a subquery, but i see that subquery right there in the query
perhaps you could begin by explaining the LEFT JOIN
it suggests that there will be rows in request_lessons that do not have a match in rating_learner_private_lessons, which kind of makes sense
that egregious â&&â operator might not be doing what you think itâs doing â try moving that clause up into the LEFT JOIN, like this, and changing it to AND â
LEFT
JOIN rating_learner_private_lessons
ON rating_learner_private_lessons.request_lesson_id = q.request_lesson_id
AND rating_learner_private_lessons.request_lesson_id = 2
so now the q grouping subquery pulls out a lot of request_lessons, but the LEFT JOIN wants to see only the rating_learner_private_lessons row for request_lesson_id 2
On top of what @r937 says, I also think you have an issue with the average query - youâre trying to pull the teachers rating, but are matching base on the user requesting the lesson, not the teacher.
Unless youâve got a lot of teachers, Iâd move the teacher rating to a sub-query and join it
SELECT lesson.request_lesson_id
, lesson.requesting_user_id
, lesson.lesson_products
, teacher_user_id as bid_user_id
, users.username AS usersname
, rating_learner_private_lessons.is_rated as is_rated
, rating_learner_private_lessons.id as rating_id
, teacherrating
FROM (SELECT request_lessons.id AS request_lesson_id
, request_lessons.user_id AS user_id
, GROUP_CONCAT(request_lesson_items.item ORDER BY request_lesson_items.item) AS product_types
FROM request_lessons
LEFT JOIN request_lesson_items ON request_lesson_items.request_lesson_id = request_lessons.id
GROUP BY request_lessons.id) AS lesson
LEFT JOIN rating_learner_private_lessons ON rating_learner_private_lessons.request_lesson_id = q.request_lesson_id
INNER JOIN bids ON bids.user_id = rating_learner_private_lessons.teacher_user_id
INNER JOIN users ON users.id = bids.user_id
LEFT JOIN (SELECT teacher_user_id, AVG(total) AS teacherrating
FROM rating_learner_private_lessons
GROUP BY teacher_user_id) rating AS rating.teacher_user_id = rating_learner_private_lessons.teacher_user_id
