title: PostgreSQL - Count Number of Classes that uses Lecture Theaters
body: <p>I have a set of distinct Lecture Theatres in a University with id (e.g. ABC1) and name (e.g. Theatre A). I want to count the number of distinct classes that uses each Lecture Theatre and rank the Lecture Theatres based on the number of classes <em>num</em> (most classes, <em>rank</em> 1 etc). Is it clear or do I need to clarify?
  <br></p>
  
  <pre class="lang-none prettyprint-override"><code>id   |   name    | num | rank 
  -----------------------------
  ABC1 | Theatre A | 42  |  1
  ABC5 | Theatre E | 37  |  2
  ABC7 | Theatre G | 25  |  3
  ABC2 | Theatre B | 25  |  4
  ABC3 | Theatre C | 10  |  5
  ABC4 | Theatre D | 9   |  6
  ABC6 | Theatre F | 0   |  7
  </code></pre>
  
  <p>So far I have managed to get the following:</p>
  
  <pre class="lang-none prettyprint-override"><code>id   |   name    
  ------------------
  ABC1 | Theatre A 
  ABC2 | Theatre B 
  ABC3 | Theatre C 
  ABC4 | Theatre D 
  ABC5 | Theatre E 
  ABC6 | Theatre F 
  ABC7 | Theatre G 
  </code></pre>
  
  <p>using the following code:</p>
  
  <pre><code>create or replace view Lecture_theatres
  AS
  SELECT distinct r.id, r.name
  FROM Rooms r
       JOIN Room_types t ON (r.rtype=t.id)
       FULL JOIN Classes c ON (c.room = r.id)
  WHERE
  t.description='Lecture Theatre'
  GROUP BY
  r.id,r.name
  ;
  </code></pre>
  
  <p>I tried to calculate <em>num</em> by adding <em>count(c.id)</em> to:</p>
  
  <pre><code>SELECT distinct r.id, r.name, count(c.id)
  </code></pre>
  
  <p>However, this seems to return the amount of times a specific class uses any Room, not the specific Lecture Theatre. I'm not sure if I am providing enough information to be able to solve this, but please comment if something is missing! </p>