title: Grouping by all columns except one to count distinct values on the remaining column
body: <p>I have <code>table A</code> with five rows and the following values:</p>
  
  <pre><code>Column1  Column2  Column3  Column4
  -------  -------  -------  -------
  anna     ben      cat      d
  anna     ben      cat      e
  anna     ben      cat      f
  gina     hugh     ken      m
  gina     hugh     ken      p
  </code></pre>
  
  <p>I want to add another column called <code>Column5</code>. The value of <code>Column 5</code> will be 3 for the first 3 rows and 2 on the next 2 rows:</p>
  
  <pre><code>Column1  Column2  Column3  Column4  Column5
  -------  -------  -------  -------  -------
  anna     ben      cat      d        3
  anna     ben      cat      e        3
  anna     ben      cat      f        3
  gina     hugh     ken      m        2
  gina     hugh     ken      p        2
  </code></pre>
  
  <p>How I did this:</p>
  
  <pre><code>SELECT DISTINCT COUNT (DISTINCT t1.Column4) AS Column5,
  Column1, Column2, Column3, Column4
  FROM TableA AS t1
  GROUP BY Column1, Column2, Column3;
  </code></pre>
  
  <p>This doesn't work:</p>
  
  <blockquote>
    <p>Msg 8120, Level 16, State 1, Procedure COUNT, Line 29<br>
    Column 'Column4' invalid in the select list because it is not contained in either an
    aggregate function or the GROUP BY clause.</p>
  </blockquote>
  
  <p>Any help please? Much appreciated. </p>
  
  <p>PS: If I add <code>Column4</code> in the group by clause, I get only values of "1" in the result table in <code>Column5</code>.</p>