title: PostgreSQL Exercise: GROUP BY clause or be used in an aggregate function
body: <p>I met a problem when doing PostgreSQL Exercises-Classify facilities by value. 
  Here is the question link: <a href="https://pgexercises.com/questions/aggregates/classify.html" rel="nofollow noreferrer">https://pgexercises.com/questions/aggregates/classify.html</a></p>
  
  <p>I tried:</p>
  
  <pre><code>select name,
    (case when rank &lt;= max(rank)/3 then 'high'
          when rank &gt; max(rank)/3 and rank &lt;= max(rank)*2/3 then 'average'
          else 'low' end) as revenue 
  from
   (select name, rank() over (order by sum(
           case when boo.memid=0 then slots*guestcost
                when boo.memid!=0 then slots*membercost end) desc) as rank
    from cd.bookings boo join cd.facilities fac on boo.facid=fac.facid
    group by name) subq
  </code></pre>
  
  <p>I also tried:</p>
  
  <pre><code>with subq as 
   (select name, rank() over (order by sum(
           case when boo.memid=0 then slots*guestcost
                when boo.memid!=0 then slots*membercost end) desc) as rank
    from cd.bookings boo join cd.facilities fac on boo.facid=fac.facid
    group by name)
  select name,
     (case when rank &lt;= max(rank)/3 then 'high'
           when rank &gt; max(rank)/3 and rank &lt;= max(rank)*2/3 then 'average'
           else 'low' end) as revenue from subq
  </code></pre>
  
  <p>Both appear error: column "sub.name" must appear in the GROUP BY clause or be used in an aggregate function</p>
  
  <p>I'm very confused and don't know what't wrong in my code. Can anybody help me?</p>