title: SQL Server How to get number of unique values in a column and average score per values?
body: <p>I have a table like this</p>
  <pre><code>city        metric_name      metric_value   id
  Berlin      likes            1              1a
  Berlin      dislikes         2              1a 
  Berlin      comments         3              1a
  
  Berlin      likes            4              1b
  Berlin      dislikes         5              1b
  Berlin      comments         3              1b
  
  Hamburg     likes            1              1c
  Hamburg     dislikes         2              1c
  Hamburg     comments         3              1c
  
  Hamburg     likes            2              1d
  Hamburg     dislikes         4              1d
  Hamburg     comments         5              1d
  </code></pre>
  <p>and so on</p>
  <pre><code>My ideal result is this
  
  city        city_count_unique   average_metric_score
  Berlin      2                   3 (sum metric_value / sum metric_names)
  Hamburg     2                   2,8
  </code></pre>
  <p>What I ve done</p>
  <p>I got distinct count for every city and avg metric value</p>
  <pre><code>SELECT AVG(T.metric_value), T.city,
  COUNT(*) AS 'city_count_unique'
  FROM 
      (SELECT DISTINCT metric_value, city
      FROM dbo.Table) as T
  GROUP BY T.city
  </code></pre>
  <p>But it is false</p>
  <p>Appreciate any help</p>
  <h1>updated</h1>
  <p>There is also an additional column id in varchar format</p>