Average calculation in MS SQL Server?

I have table in MS SQL Server like below:

ID    | CLIENT
--------------
123   | D    
444   | D    
555   | E   

And I need to calculate how many ID is per CLIENT. SO as a result I need something like below:

CLIENT | avg_id_per_client
---------------------------
D      | 1.5
E      | 3

• D = 1.5 because there are 2 CLIENTS "D" and there are 3 CLIENTS in general, so 3 / 2 = 1.5
• E = 3 because there are 1 CLIENT "E" and there are 3 CLIENTS in general, so 3 / 1 = 3

How to do it in MS SQL Server ?

Solution 1:

You can SUM OVER a COUNT.

SELECT CLIENT
, COUNT(id) AS client_total_id
, CAST(AVG(1.0*COUNT(id)) OVER () AS FLOAT) AS average_count
, CAST(1.0*SUM(COUNT(id)) OVER () / COUNT(id) AS FLOAT) AS odd_avg
, CAST(100.0*COUNT(id) / SUM(COUNT(id)) OVER () AS DECIMAL(5,2)) 
AS percentage_id
FROM your_table
GROUP BY client

But you probably want this

SELECT CAST(AVG(1.0*client_total_id) AS FLOAT) AS avg_id_per_client
FROM
(
  SELECT client, COUNT(id) AS client_total_id
  FROM your_table
  GROUP BY client
) q;

db<>fiddle here