SQL Server How to get number of unique values in a column and average score per values?

I have a table like this

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

and so on

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

What I ve done

I got distinct count for every city and avg metric value

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

But it is false

Appreciate any help

updated

There is also an additional column id in varchar format

Answer

The answer here depends on this assumption:

You always have exactly 3 metrics per ‘group’ (i.e. likes, dislikes and comments)

If that assumption is correct, the following will output what you are looking for:

SELECT  city,
        COUNT(metric_name) / 3 AS city_count_unique,
        CAST(SUM(metric_value) AS FLOAT) / COUNT(metric_value) AS average_metric_score
  FROM  #Table
  GROUP BY city

Output:

city    city_count_unique   average_metric_score
Berlin  2                   3
Hamburg 2                   2.83333333333333

How does this work?

By grouping on the city, we combine results for each city individually.

The count of metric_name gives the total metrics for that city (which is 6 in the case of your example). I divide this by 3 to give the unique count (based on the assumption I stated).

The average_metric_score calculation if the total of the metric_value for each city divided by the number of metrics (so 18 / 6 for Berlin). The reason for the CAST to FLOAT is to allow for a floating point answer. You could also use CONVERT if you prefer this to CAST.

Edit following OP update to question

OP edited the question to indicate that there is an ID column that allows the detection of metric grouping. This is an update to use that rather than assuming there are always 3 metrics per group.

SELECT  city,
        COUNT(id) AS city_count_unique,
        CAST(SUM(metric_value_total) AS FLOAT) / SUM(metric_value_count) AS average_metric_score
  FROM  (
        SELECT  city,
                id,
                SUM(metric_value) metric_value_total,
                COUNT(metric_value) AS metric_value_count
          FROM  #Table
          GROUP BY city, id
        ) a
  GROUP BY city