CODEWITHSUNDEEP
mysqlsql
Patrick Leisegang
Patrick Leisegang

Reputation: 23

Select average from join MYSQL

I am trying to get an average on the last join:

INNER JOIN rating r 
    ON r.rid = (SELECT MAX(rid) FROM rating WHERE uid = u.uid ORDER BY rid DESC LIMIT 1)

I am having some problems getting it to give me a rating with 1 decimal like 4.3 in rating. How can i do this in a simple way?

SELECT p.uid, u.name, u.description, u.url, u.picurl, u.mapurl, p.pid, p.price, r.rid, r.rating FROM utested u 
INNER JOIN price p 
    ON p.pid = (SELECT MAX(pid) FROM price WHERE uid = u.uid ORDER BY uid DESC LIMIT 1)
INNER JOIN rating r 
    ON r.rid = (SELECT MAX(rid) FROM rating WHERE uid = u.uid ORDER BY rid DESC LIMIT 1)
ORDER BY u.name;

I have created a sql fiddle so you can try out the queries. http://sqlfiddle.com/#!2/93b771/1

Upvotes: 1

Views: 98

Answers (1)

Strawberry
Strawberry

Reputation: 33945

Consider the following. How does this result differ from the desired result?

+-----+------------+-------------+----------+------------+--------------+-----+-------+-----+--------+
| uid | name       | description | url      | picurl     | mapurl       | pid | price | rid | rating |
+-----+------------+-------------+----------+------------+--------------+-----+-------+-----+--------+
|   5 | Havana Pub |             |          |            |              |  35 |    74 |  11 |      5 |
|   3 | Hos Naboen |             |          |            |              |  33 |    74 |   9 |      5 |
|   2 | Javel      | Musikk      | javel.no | pic.jave.. | map.javel.no |  38 |    88 |   8 |      5 |
|   1 | Kick       | Yay         | kick.no  | http://p.. | map.kick.no  |  31 |    74 |  15 |      1 |
|   6 | Leopold    |             |          |            |              |  36 |    74 |  12 |      5 |
|   4 | Victoria   |             |          |            |              |  37 |    75 |  10 |      5 |
+-----+------------+-------------+----------+------------+--------------+-----+-------+-----+--------+

OK. I'm going to take a wild stab in the dark here...

 SELECT p.uid
      , u.name
      , u.description
      , u.url
      , u.picurl
      , u.mapurl
      , p.pid
      , p.price
      , AVG(r.rating) rating
   FROM utested u
   JOIN price p
     ON p.uid = u.uid
   JOIN ( SELECT uid, MAX(pid) latest_price FROM price GROUP BY uid ) px
     ON px.uid = p.uid
    AND px.latest_price = p.pid
   JOIN rating r
     ON r.uid = u.uid
  GROUP
     BY u.name;
 +-----+------------+-------------+----------+--------------+--------------+-----+-------+--------+
 | uid | name       | description | url      | picurl       | mapurl       | pid | price | rating |
 +-----+------------+-------------+----------+--------------+--------------+-----+-------+--------+
 |   5 | Havana Pub |             |          |              |              |  35 |    74 | 5.5000 |
 |   3 | Hos Naboen |             |          |              |              |  33 |    74 | 4.0000 |
 |   2 | Javel      | Musikk      | javel.no | pic.javel... | map.javel.no |  38 |    88 | 5.0000 |
 |   1 | Kick       | Yay         | kick.no  | http://pri.. | map.kick.no  |  31 |    74 | 3.4000 |
 |   6 | Leopold    |             |          |              |              |  36 |    74 | 3.5000 |
 |   4 | Victoria   |             |          |              |              |  37 |    75 | 4.0000 |
 +-----+------------+-------------+----------+--------------+--------------+-----+-------+--------+

Upvotes: 1

Related Questions