1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27
|
-- determine nb msg max among members
CREATE TABLE #MSG_PER_MEMBER
(
pseudo varchar(64),
nb_msg int
)
INSERT INTO #MSG_PER_MEMBER
SELECT pseudo, count(id_chat) as nb_msg FROM chat GROUP BY pseudo
DECLARE @nb_msg_member_max float
SET @nb_msg_member_max = cast((SELECT max(nb_msg) FROM #MSG_PER_MEMBER) as float)
-- perform select
SELECT
pseudo,
count(*) as nb_messages,
round((count(*) / @nb_msg_member_max), 2) as [percent]
FROM
chat
GROUP BY
pseudo
ORDER BY
nb_messages desc
DROP TABLE #MSG_PER_MEMBER |
Partager