title: sql server select into variable and count result
body: <p>Something very simple, but I cannot get it working for me :)</p>
  
  <pre><code>select x.name, count(x.name) from &lt;table_name&gt; x where ...&lt;complex and long&gt;...
  </code></pre>
  
  <p>It throws an error, that the x.name isn't used with group by.</p>
  
  <pre><code>select x.name from &lt;table_name&gt; x where ...&lt;complex and long&gt;...
  </code></pre>
  
  <p>works fine and returns e.g. 6 names</p>
  
  <pre><code>select count(x.name) from &lt;table_name&gt; x where ...&lt;complex and long&gt;...
  </code></pre>
  
  <p>works also fine and returns e.g. the number 6</p>
  
  <p>But the combination is not working, when I tried to add the group by:</p>
  
  <pre><code>select x.name, count(x.name) from &lt;table_name&gt; x where ...&lt;complex and long&gt;...
  group by x.name
  </code></pre>
  
  <p>it worked, but all counts were 1 and not 6.</p>
  
  <p>The thing is, I could firstly get the count into variable, then write the long sql statement just to get the names, but I don't want to write the long complex select statement twice. There has to be some way to do the combination in one select: get all names and by the way tell me how many they were.</p>
  
  <p>Thanks</p>
  
  <p>P.S.</p>
  
  <pre><code>name    bla1    bla2
  a       ...     ...     
  a       foo     ...     
  b       foo     ...     
  c       ...     ...     
  d       foo     ...     
  d       foo     ...     
  b       foo     ...     
  c       foo     ...     
  </code></pre>
  
  <p>The result of x.name where bla1 = foo would be:</p>
  
  <pre><code>a
  b
  d
  d
  b
  c
  </code></pre>
  
  <p>The result of count(x.name) where bla1 = foo would be:</p>
  
  <pre><code>6
  </code></pre>
  
  <p>My desired result of:</p>
  
  <pre><code>...variable definitions
  select @foundName = x.name, @numOfAllFoundNames = count(x.name)
  from &lt;table_name&gt; x where ...&lt;complex and long&gt;...
  </code></pre>
  
  <p>should be: 
  @foundName = a (just one of the names, no matter which one)
  @numOfAllFoundNames = 6</p>