Getting an Average

[jtrope]

Problem
I am trying to get all records related to girls_id , as well as their average rating.

Main Girls Table
Table Name: "girls"
Desc: Each girl has their own unique record.

+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| girls_id       | int(11)      | NO   | PRI | NULL    | auto_increment |
| girls_name     | varchar(255) | NO   |     | NULL    |                |
| girls_isActive | int(11)      | NO   |     | NULL    |                |
| girls_city     | varchar(255) | YES  |     | NULL    |                |
| girls_desc     | longtext     | YES  |     | NULL    |                |
| girls_photo    | varchar(255) | YES  |     | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+

Main Girls Rating Table
Table Name: "rating"
Desc: Each girl can have many records in this table.

+-----------------+---------+------+-----+---------+----------------+
| Field           | Type    | Null | Key | Default | Extra          |
+-----------------+---------+------+-----+---------+----------------+
| rating_id       | int(11) | NO   | PRI | NULL    | auto_increment |
| girls_id        | int(11) | NO   |     | NULL    |                |
| rating          | int(11) | NO   |     | NULL    |                |
| rated_by_userid | int(11) | NO   |     | NULL    |                |
+-----------------+---------+------+-----+---------+----------------+

EVEN BETTER

Even better would be to add a WHERE clause defining the girls_id , so I can call this at will for an individual person.

Thanks for helping with my first post!

 

[r937]

what does "get all records, as well as their average rating" mean?

which records? from both tables?

if you are retrieving the details of the ratings, you want the average too?

can you show a few sample rows of the expected result set?

p.s. i would remove rating_id, and make the PK of the rating table (girls_id,rated_by_userid)

r937.com | rudy.ca

 

[Kozusnik]

Maybe something like...

SELECT *,AVG(t2.rating)
  FROM girls t1
  LEFT OUTER JOIN rating t2 on t1.girls_id=t2.girls_id
  GROUP BY t2.girls_id,t2.rated_by_userid;
{/CODE]

You could add the WHERE after the JOIN, but you'd have to do that in the program. Also, this could be a sub-select so you could sort it by their name like...

[CODE]
SELECT * FROM (
  SELECT *,AVG(t2.rating)
  FROM girls t1
  LEFT OUTER JOIN rating t2 on t1.girls_id=t2.girls_id
  GROUP BY t2.girls_id,t2.rated_by_userid
)
ORDER BY girls_name
{/CODE]

Mark

 

[Kozusnik]

Some day I'll learn to type I promise.

[ != {

Mark