Such as data

The sender to the recipient
A 1
A 1
A 2
A 3
4 B
4 B
5 C
6 D
7 D
E 8
F 8
9 F
G 0

How to statistics the sender a total of how much a letter, and the sender to the recipient, number after heavy
If the calculated results
Further to sender counting different recipient
A 4 3
B 2 1
C 1 1
2 2 D
E 1 1
F 2 2
G 1 1


Seek help from a great god

CodePudding user response:

 
DECLARE @ t TABLE (the sender NVARCHAR (10) NOT NULL, the recipient NVARCHAR (10) NOT NULL) 
INSERT @ t (the sender and recipient) VALUES 
(' A ', '1'), (' A ', '1'), (' A ', '2'), (' A ', '3') and (' B ', '4'), 
(' B ', '4'), (' C ', '5'), (' D ', '6'), (' D ', '7'), (' E ', '8'), 
(' F ', '8'), (' F ', '9'), (' G 'and' 0 ') 

SELECT the sender, 
COUNT (*) AS the sender number, 
(SELECT COUNT (*) FROM (SELECT DISTINCT recipient FROM @ t SJR WHERE SJR. The sender=FJR. The sender) b) AS different recipient COUNT 
The FROM @ t FJR 
GROUP BY the sender; 

CodePudding user response:

Thank you thank you, but I would like to ask, just above example, my data is very much, and I don't know how many the sender and recipient, how to write? The table name ABC

Ask next, the recipient is how statistics?
SELECT the sender,
COUNT (*) AS the sender number,
(SELECT COUNT (*) FROM (SELECT DISTINCT recipient FROM @ t SJR WHERE SJR. The sender=FJR. The sender) b) AS different recipient COUNT
The FROM @ t FJR
GROUP BY the sender;