title: Athena row_number function using in combination with where or count
body: <p>Bit of a newbie here and struggling to get to grips with what should be rudimentary tasks.  </p>
  
  <p>I have a simple table with products, many of which have multiple colours. I want to know how many products have more than three colours. </p>
  
  <p>I started with something like the below:</p>
  
  <pre><code>SELECT product_colour, 
         product_id,
         row_number() over (partition by product_colour) AS row_num
  FROM table
  WHERE row_num &gt; 3
  ORDER BY product_id
  </code></pre>
  
  <p>The above did not work, because of an outer/inner logic in SQL, Googled it earlier, so misinterpretation is likely. </p>
  
  <p>I attempted this, having followed a tutorial.</p>
  
  <pre><code>SELECT rn,
         product_colour,
         product_id,
  FROM 
  (
    SELECT row_number() over (partition by product_colour ORDER BY product_colour) AS rn,
           product_colour,
           product_id,
    FROM table
  ) AS t
  HAVING COUNT(t.rn) &gt; 3
  </code></pre>
  
  <p>No matter what combinations of syntax "stuff" I try in the vein hope of getting past this error message "'"rn"' must be an aggregate expression or appear in GROUP BY clause". I simply cannot do it. </p>
  
  <p>Having been inspired by the first responder, I have managed to get the closest yet, however, this query no longer returns any error messages, just an empty table. </p>
  
  <pre><code>SELECT product_colour,
         t.rn,
         product_id,
  FROM 
  (
    SELECT row_number() over (partition by product_colour) AS rn,
           product_colour,
           product_id
    FROM table
  ) AS t
  GROUP BY product_colour, product_id, t.rn
  HAVING COUNT(t.rn) &gt; 3
  ORDER BY product_colour, t.rn
  </code></pre>
  
  <p>Any ideas? </p>