Group query help

Transact-SQL
1

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

From_user touser
1 2
2 1
1 2
2 1
3 1
1 3
1 4

2 
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
3

i want group instead of single user. means if user 1 and 2 chat together than count of both send and recive message shoud be there in one row.

4

what is the expected result from the sample data that you have posed ?

5

With:

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;