i have a chat table like below that contains messgae sent user and recieved user and i want to get count of
chats according to user. like if user 1 sends 5 messgaes to user 2 and user user 2 sends 3 messages to user then this should be counted as
8 as result. please help
select user, cnt = sum(cnt)
from
(
select user = from_user , cnt = count(*) from table group by from_user
union all
select user = to_user , cnt = count(*) from table group by to_user
) c
group by user
CREATE TABLE #t
(
From_User int NOT NULL
,To_User int NOT NULL
);
INSERT INTO #t
VALUES (1, 2),(2, 1),(1, 2),(2, 1),(3, 1),(1, 3),(1, 4);
Maybe:
WITH OrderedUsers
AS
(
SELECT
CASE WHEN From_User > To_User THEN To_User ELSE From_User END AS User1
,CASE WHEN From_User > To_User THEN From_User ELSE To_User END AS User2
FROM #t
)
SELECT User1, User2, COUNT(*) AS Chats
FROM OrderedUsers
GROUP BY User1, User2;
or
WITH OrderedUsers
AS
(
SELECT From_User, To_User
,CASE WHEN From_User > To_User THEN To_User ELSE From_User END AS User1
,CASE WHEN From_User > To_User THEN From_User ELSE To_User END AS User2
FROM #t
)
SELECT From_User, To_User, COUNT(*) OVER (PARTITION BY User1, User2) AS Chats
FROM OrderedUsers;