title: percentile functions with GROUPBY in BigQuery
body: <p>In my CENSUS table, I'd like to group by State, and for each State get the median county population and the number of counties.</p>
  
  <p>In psql, redshift, and snowflake, I can do this:</p>
  
  <pre><code>psql=&gt; SELECT state, count(county), PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY "population2000") AS median FROM CENSUS GROUP BY state;
          state         | count |  median
  ----------------------+-------+----------
   Alabama              |    67 |    36583
   Alaska               |    24 |   7296.5
   Arizona              |    15 |   116320
   Arkansas             |    75 |    20229
  ...
  </code></pre>
  
  <p>I'm trying to find a nice way to do this in standard BigQuery. I've noticed that there's undocumented <code>percentile_cont</code> analytical function available, but I have to do some major hacks to get it to do what I want.</p>
  
  <p>I'd like to be able to do the same sort thing with what I've gathered are the correct arguments:</p>
  
  <pre><code>SELECT
    state,
    COUNT(county),
    PERCENTILE_CONT(population2000,
      0.5) OVER () AS `medPop`
  FROM
    CENSUS
  GROUP BY
    state;
  </code></pre>
  
  <p>but this query yields the error</p>
  
  <pre><code>SELECT list expression references column population2000 which is neither grouped nor aggregated at
  </code></pre>
  
  <p>I <em>can</em> get the answer I want, but I'd be very disappointed if this is the recommended way to do what I want to do:</p>
  
  <pre><code>SELECT
    MAX(nCounties) AS nCounties,
    state,
    MAX(medPop) AS medPop
  FROM (
    SELECT
      nCounties,
      T1.state,
      (PERCENTILE_CONT(population2000,
          0.5) OVER (PARTITION BY T1.state)) AS `medPop`
    FROM
      census T1
    LEFT OUTER JOIN (
      SELECT
        COUNT(county) AS `nCounties`,
        state
      FROM
        census
      GROUP BY
        state) T2
    ON
      T1.state = T2.state) T3
  GROUP BY
    state
  </code></pre>
  
  <p>Is there a better way to do what I want to do? Also, is the <code>PERCENTILE_CONT</code> function ever going to be documented?</p>
  
  <p>Thanks for reading!</p>