title: Postgres using ORDER BY on an AVG function
body: <p>I am new to Postgres, and I am trying to join tables, get average values and order by average values. The 2 tables are described below:</p>
  
  <pre><code>Table "public.movies"
     Column    |  Type   | Modifiers | Storage  | Stats target | Description 
  -------------+---------+-----------+----------+--------------+-------------
   movieid     | integer | not null  | plain    |              | 
   title       | text    |           | extended |              | 
   releasedate | date    |           | plain    |              | 
   url         | text    |           | extended |              | 
   unknown     | integer |           | plain    |              | 
   action      | integer |           | plain    |              | 
   adventure   | integer |           | plain    |              | 
   animation   | integer |           | plain    |              | 
   children    | integer |           | plain    |              | 
   comedy      | integer |           | plain    |              | 
   crime       | integer |           | plain    |              | 
   documentary | integer |           | plain    |              | 
   drama       | integer |           | plain    |              | 
  
  
  
  Table "public.ratings"
   Column  |  Type   | Modifiers | Storage | Stats target | Description 
  ---------+---------+-----------+---------+--------------+-------------
   userid  | integer | not null  | plain   |              | 
   movieid | integer | not null  | plain   |              | 
   rating  | integer |           | plain   |              | 
   time    | integer |           | plain   |              | 
  Indexes:
      "ratings_pkey" PRIMARY KEY, btree (userid, movieid)
  Foreign-key constraints:
      "ratings_movieid_fkey" FOREIGN KEY (movieid) REFERENCES movies(movieid)
  Has OIDs: no
  </code></pre>
  
  <p>I have to order by the average ratings as one movieid has multiple ratings from different users, but I have to order by (asc) the average ratings. My query is as below,  and works fine without ORDER BY:</p>
  
  <pre><code> SELECT (movies.title, AVG(ratings.rating)) 
   FROM movies 
      FULL JOIN ratings ON movies.movieid = ratings.movieid 
   WHERE (movies.comedy = 1) GROUP BY movies.title LIMIT 10;
  </code></pre>
  
  <p>It however returns 1.00000000000 for ALL the movie ratings if I use: <code>ORDER BY AVG(ratings.rating)</code></p>
  
  <p>Any help will be really appreciated! Thanks in advance :)</p>