给一张table；

+----+----------+
| id | modality |
+----+----------+
| 1  | CT       |
| 1  | SC       |
| 1  | MR       |
| 1  | CT       |
| 2  | SC       |
| 3  | CT       |
| 3  | CT       |
| 3  | MR       |
| 4  | MR       |
| 4  | CT       |
+----+----------+

有点像；

SELECT AVG(modality_count) 
FROM (
SELECT id, COUNT(modality) AS modality_count
FROM TABLE
GROUP BY id
    ) a

将获得每个患者的平均检查量，并输出如下内容：

+---------------------+
| AVG(modality_count) |
+---------------------+
| 1.4                 |
+---------------------+

以下查询：

SELECT CONCAT(CAST(COUNT(*) AS string), ' ' , modality) AS count_m
FROM TABLE
GROUP BY modality

将获得每个成像设备的行数，如：

+---+---------+
|   | count_m |
+---+---------+
| 1 | 560 CT  |
| 2 | 2 SC    |
| 3 | 473 MR  |
+---+---------+

假设有1035行，726个唯一id，每个id可能有1.4次扫描（ct，sc，mr）。您如何获得ct/mt/sc的平均数量？
期望的输出类似于：

+----------+---------+
| modality | AVG(id) |
+----------+---------+
| CT       | 0.77    |
| SC       | 0.003   |
| MR       | 0.65   |
+----------+---------+

提前谢谢