An SQL statement used to count the number of people with the same name

SQL statement Query
The table structure is as follows:
ID name gender
1 Michael
2. Wang Si male
3 Lili female
4 three male
5 Zhao liunan
6 Gao Jie male
7. Wang 4 female
8 Gao Jie female
9 three women
How can I use an SQL statement to query the following results?

Number of persons by name number of men Number of women including ID
Zhang San 3 2 1, 9
Wang Si 2 1
Lili 1 0 1 3
Gao Jie 2 1 1 6, 8
Zhao Liu 1 1 0 5
Copy codeThe Code is as follows:
SELECT * FROM (select distinct Name, Count (ID) as number of people,
Sum (case when Sex = 'male' then 1 else 0 end) as men,
Sum (case when Sex = 'female' then 1 else 0 end) as female count
FROM Test group by Name)
Outer apply (
SELECT
[Include ID] = STUFF (REPLACE (
(
Select id from Test N
WHERE Name = A. Name
FOR XML AUTO
), '<N id = "', ','), '"/>', ''), 1, 1 ,'')
) N