title: Oracle SQL group by single field and count the grouped rows
body: <p>Consider this table <code>some_table</code>:</p>
  
  <pre><code>+--------+----------+---------------------+-------+
  | id     | other_id | date_value          | value |
  +--------+----------+---------------------+-------+
  | 1      | 1        | 2011-04-20 21:03:05 | 104   |
  | 2      | 1        | 2011-04-20 21:03:04 | 229   |
  | 3      | 3        | 2011-04-20 21:03:03 | 130   |
  | 4      | 1        | 2011-04-20 21:02:09 | 97    |
  | 5      | 2        | 2011-04-20 21:02:08 | 65    |
  | 6      | 3        | 2011-04-20 21:02:07 | 101   |
  | ...    | ...      | ...                 | ...   |
  +--------+----------+---------------------+-------+
  </code></pre>
  
  <p>I want to select and group by the <code>other_id</code>, so that I only get unique <code>other_id</code>s. This query works (credit @MichaelPakhantsov):</p>
  
  <pre><code>select id, other_id, date_value, value from
   (
     SELECT id, other_id, date_value, value, 
     ROW_NUMBER() OVER (partition by other_id order BY Date_Value desc) r
     FROM some_table 
   )
   where r = 1
  </code></pre>
  
  <p>How can I get the same result, but with a count of how many rows were grouped, for each <code>other_id</code>. The desired result would look like:</p>
  
  <pre><code>+--------+----------+---------------------+-------+-------+
  | id     | other_id | date_value          | value | count |
  +--------+----------+---------------------+-------+-------+
  | 1      | 1        | 2011-04-20 21:03:05 | 104   | 3     |
  | 5      | 2        | 2011-04-20 21:02:08 | 65    | 2     |
  | 3      | 3        | 2011-04-20 21:03:03 | 130   | 2     |
  +--------+----------+---------------------+-------+-------+
  </code></pre>
  
  <p>I've tried using <code>COUNT(other_id)</code> in both the inner and outer selects but it produces this error:</p>
  
  <blockquote>
    <p>ORA-00937: not a single-group group function</p>
  </blockquote>
  
  <hr>
  
  <p>Note: similar to <a href="https://stackoverflow.com/questions/5815601/oracle-sql-group-by-not-a-group-by-expression-help">this question</a> (example table and answer taken from there) but that question doesn't give a count of the collapsed rows.</p>