Case statement COUNT for THEN in (PostgreSQL) 9.3.11
<p>Firstly, this is a coursework question, so I am not looking for a full answer, just a hint :)</p>

<p>I have a "monarch" database that stores name, house(?), accession, coronation(?) that keeps track of monarchs (including prime ministers). The house and coronation applies only to monarchs and returns null if monarch is a prime minister.
It looks as follows:</p>

<p><a href="https://i.stack.imgur.com/5q6R5.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/5q6R5.png" alt="table"></a></p>

<p>I am required to write a psql query that returns the scheme(house,seventeenth,eighteenth,nineteenth,twentieth), listing the number of monarchs of each royal house that acceded to the throne in the 17th,18th,19th and 20th centuries and have an issue of what to add as my THEN query.</p>

<p><strong>EDIT:</strong> 
Thank you for your suggestions! I made some changes to my query now:</p>

<pre><code> SELECT  house,
         TO_CHAR(accession, 'YYYY' ) AS accession_year,
         COUNT(CASE WHEN accession_year BETWEEN 1601 AND 1700 THEN name END) AS seventeenth,
         COUNT(CASE WHEN accession_year BETWEEN 1701 AND 1800 THEN name END) AS eighteenth,
         COUNT(CASE WHEN accession_year BETWEEN 1801 AND 1900 THEN name END) AS nineteenth,
         COUNT(CASE WHEN accession_year BETWEEN 1901 AND 2000 THEN name END) AS twentieth,
  FROM monarch
  WHERE house IS NOT NULL
  GROUP BY house
  ;
</code></pre>

<p>Now psql tells me that accession_year does not exist. I do not want to use full accession date in CASE statements. How can I still use my TO_CHAR in the query?</p>