与 SQL 中的列总和相关的每行的百分比值

Question

我有一个查询，看起来像这样：

SELECT dbo.music_styles.music, (COUNT(dbo.people.id)* 100.00 / (SELECT SUM(dbo.people.id)))  AS nbStyle
FROM dbo.people_music_styles
INNER JOIN dbo.music_styles ON dbo.music_styles.id = dbo.people_music_styles.id_music_styles
WHERE dbo.people.age BETWEEN @age_min and @age_max 
AND dbo.people.gender = @gender
GROUP BY dbo.music_styles.music
ORDER BY nbStyle DESC

在响应中，我得到一个包含两列的数组：

• 一个带有样式名称(感谢连接(
• 另一列，其中包含样式在关系表中与 I 设置的参数相关的出现次数。

我想要第三列，其中包含有关该列的总和/总和的行值以及样式出现的次数。

Answer 1

由于这需要分数，请确保进行转换，使其不会截断为整数：

(COUNT(DISTINCT dbo.music_styles.id) / CONVERT(FLOAT, COUNT(SELECT SUM(dbo.music_styles) FROM dbo.music_styles))) * 100

让我知道这是否有效，如果没有，我很乐意进一步探索。

Answer 2

@CarlosBos我终于设法找到了一个可以使用您的部分建议的答案！ 这是代码：

SELECT dbo.music_styles.music, COUNT(dbo.people.id) AS peopleID, CONVERT(FLOAT, ROUND(COUNT(dbo.people.id)* 100.00 / SUM(COUNT(dbo.people.id)) OVER (), 2)) AS Répartition
FROM dbo.people_music_styles
INNER JOIN dbo.people ON dbo.people.id = dbo.people_music_styles.id_people
INNER JOIN dbo.music_styles ON dbo.music_styles.id = dbo.people_music_styles.id_music_styles
WHERE dbo.people.age BETWEEN @age_min and @age_max 
AND dbo.people.gender = @gender
GROUP BY dbo.music_styles.music
ORDER BY peopleID DESC

多谢！