title: SQL aggregate function alias
body: <p>I'm a beginner at SQL and this is the question I have been asked to solve:</p>
  
  <blockquote>
    <p>Say that a big city is defined as a <code>place</code> of type <code>city</code> with a population of at
    least 100,000. Write an SQL query that returns the scheme <code>(state_name,no_big_city,big_city_population)</code> ordered by <code>state_name</code>, listing those states which have either (a) at least five big cities or (b) at least one million people living in big cities. The column <code>state_name</code> is the <code>name</code> of the <code>state</code>, <code>no_big_city</code> is the number of big cities in the state, and <code>big_city_population</code> is the number of people living in big cities in the state.</p>
  </blockquote>
  
  <p>Now, as far as I can see, the following query returns correct results:</p>
  
  <pre><code>SELECT state.name AS state_name
       , COUNT(CASE WHEN place.type = 'city' AND place.population &gt;= 100000 THEN 1 ELSE NULL END) AS no_big_city
       , SUM(CASE WHEN place.type = 'city' AND place.population &gt;= 100000 THEN place.population ELSE NULL END) AS big_city_population
  FROM state
  JOIN place
  ON state.code = place.state_code
  GROUP BY state_name
      HAVING
          COUNT(CASE WHEN place.type = 'city' AND place.population &gt;= 100000 THEN 1 ELSE NULL END) &gt;= 5 OR
          SUM(CASE WHEN place.type = 'city' AND place.population &gt;= 100000 THEN place.population ELSE NULL END) &gt;= 1000000
  ORDER BY state_name;
  </code></pre>
  
  <p>However, the two aggregate functions used in the code appear twice. MY question: <strong>is there any way of making this code duplication disappear preserving functionality?</strong></p>
  
  <p>To be clear, I have already tried using the alias, but I just get a "column does not exist" error.</p>