Average Query - select one row but get full column values

Hi

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.

Any suggestions?

thanks :slight_smile:
Karen

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.

Hi

Sorry - here is the full query

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

As the query has a lot of ‘noise’ I was trying to hone in on the relevant parts :slight_smile:

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?

agreed … it makes mine melt :slight_smile:

I’m trying to get the data from

  1. Request lesson - here we put the lesson details
  2. 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.

  1. We need to find the teacher who is hired - joined with ‘Bid’ (teacher who succesasfully bid)
  2. Get the username of the teacher who bid found in users
  3. 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)
  4. 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.

thanks
Karen

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

what is your problem with the above?

1 Like

When I echo out the query, it appears that q.user_id is not rendered as 2.

also the results are null when there is a rating for teacher in the rating_learner table

thanks
Karen

This query works fine:

SELECT avg(teacher_user_id) FROM rating_learner_private_lessons WHERE teacher_user_id=2

ps perhaps mixing indiviual and group records is not such a clever idea, I can run two separate queries as an option

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

does that make sense?

Ok I will give it a try. That makes sense
but I just got interrupted (children ).

Apologies, I will have to return in a few hours.

I thank you all for your kind attention.

:slight_smile:Karen

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

Ok - I will have a look at those options

ps - this is the beta site www.eloorn.com

Maybe that will help it make sense.

it all works except the rating stars - I taught myself so (I am sure filled with holes)

I will try these options later today.

thanks,
Karen

Hi All,

I put my poor brain to good work on this one and despite the steam pouring out from the ears, was unable to complete successfully.

Ultimately I performed two separate queries (in my defence possibly one of the few on the site).

Thanks ever so much = I will return to this space at a later date and see if I can yet bend this query to submission… or not :slight_smile:
Karen