如何在SQL UNION查询中用COUNT(*)分组统计并显示对应用户名

你当前的查询是对UNION合并后的全量结果做全局计数，因此只能得到1个总行数。要实现按用户维度统计数量，只需要做两处调整：

• 统一UNION两个分支中用户姓名列的别名：你当前第一个分支的cn1.FullName别名为[Name]，第二个分支同位置的cn1.FullName别名为[Provider Name]，UNION按列位置合并虽然不会报错，但后续外层引用字段易出错，建议统一为相同别名（比如[User]）。
• 修改外层聚合逻辑：将全局COUNT(*)改为按用户名字段分组统计，最后增加排序规则即可。

修正后的完整SQL如下：

SELECT 
    COUNT([Encounter ID]) as [Open Notes],
    [User]
FROM 
(
    SELECT DISTINCT(pe.EncounterID) AS [Encounter ID], 
        cn1.FullName AS [User],
        convert(varchar, pe.EncounterDate, 101) AS [Encounter Date],
        cn2.FullName AS [P Name], 
        p.AccountNumber AS [P ID],
        convert(varchar, pe.EncounterDate, 22) AS [Note Date],
        pv.memo AS [Note Memo],
        es.StateName AS [Note Status]
    FROM [exP].[dbo].[Encounter] pe
    INNER JOIN [exP].[dbo].[Visit] pv 
        ON pv.PEncounterID = pe.PEncounterID
    INNER JOIN [exP].[dbo].[UserProfile] up
        ON up.UserProfileID = pe.phID 
    INNER JOIN [exP].[dbo].[ContactName] cn1
        ON cn1.ContactInfoID = up.ContactInfoID
    INNER JOIN [exP].[dbo].[Event] e
        ON e.EventID = pe.EventID
    INNER JOIN [exP].[dbo].[EventStates] es
        ON es.EventStateID = e.State
    LEFT OUTER JOIN [exP].[dbo].[P] p
        ON p.PID = pe.PID
    LEFT OUTER JOIN [exP].[dbo].[ContactName] cn2
        ON cn2.ContactInfoID = p.ContactInfoID
    WHERE (up.Type = 200 OR up.Type = 205 OR up.Type = 206)
        AND pe.EncounterDate >= '2022-06-01'
        AND pe.EncounterDate < getdate()
        AND pe.BillingState = 0
        AND e.State <> 3
        AND e.State <> 4
        AND pv.VisitID <> 263
        AND pv.VisitID <> 265 
        AND pv.VisitID <> 549 
        AND pv.VisitID <> 29564
        AND pv.ReasonID <> 1143
        AND pv.ReasonID <> 70390
        AND pv.ReasonID <> 70426
        AND pv.ReasonID <> 65756
        AND pv.ReasonID <> 65767
        AND pv.Memo NOT LIKE '%ss only%'
        AND pv.Memo NOT LIKE '%s only%'
        AND pe.TypeID <> 57
        AND pe.TypeID <> 60
        AND pe.TypeID <> 61
        AND pe.TypeID <> 62
        AND pe.TypeID <> 66
        AND pe.TypeID <> 67
        AND pe.TypeID <> 68
        AND pe.TypeID <> 78
        AND pe.TypeID <> 79
        
    UNION

    SELECT DISTINCT(pe.PEncounterID) AS [Encounter ID], 
        cn1.FullName AS [User],
        convert(varchar, pe.EncounterDate, 101) AS [Encounter Date],
        cn2.FullName AS [P Name], 
        p.AccountNumber AS [P ID],
        convert(varchar, pe.EncounterDate, 22) AS [Note Date],
        pv.memo AS [Note Memo],
        es.StateName AS [Note Status]
    FROM [exP].[dbo].[PEncounter] pe
    INNER JOIN [exP].[dbo].[PVisit] pv 
        ON pv.PEncounterID = pe.PEncounterID
    INNER JOIN [exP].[dbo].[UserProfile] up
        ON up.UserProfileID = pe.phID
    INNER JOIN [exP].[dbo].[ContactName] cn1
        ON cn1.ContactInfoID = up.ContactInfoID
    LEFT JOIN [exP].[dbo].[Event] e
        ON e.PID = pe.PID
    INNER JOIN [exP].[dbo].[EventStates] es
        ON es.EventStateID = e.State
    LEFT OUTER JOIN [exP].[dbo].[P] p
        ON p.PID = pe.PID
    LEFT OUTER JOIN [exP].[dbo].[ContactName] cn2
        ON cn2.ContactInfoID = p.ContactInfoID
    WHERE (up.Type = 200 OR up.Type = 205 OR up.Type = 206)
        AND pe.EncounterDate >= '2022-06-01'
        AND pe.EncounterDate < getdate()
        AND pe.EventID is null
        AND e.State <> 3
        AND e.State <> 4
        AND pv.VisitID <> 263
        AND pv.VisitID <> 265 
        AND pv.VisitID <> 549 
        AND pv.VisitID <> 29564
        AND pv.ReasonID <> 1143
        AND pv.ReasonID <> 70390
        AND pv.ReasonID <> 70426
        AND pv.ReasonID <> 65756
        AND pv.ReasonID <> 65767
        AND pv.Memo NOT LIKE '%ss only%'
        AND pv.Memo NOT LIKE '%s only%'
        AND pe.TypeID <> 57
        AND pe.TypeID <> 60
        AND pe.TypeID <> 61
        AND pe.TypeID <> 62
        AND pe.TypeID <> 66
        AND pe.TypeID <> 67
        AND pe.TypeID <> 68
        AND pe.TypeID <> 78
        AND pe.TypeID <> 79
) as ct
GROUP BY [User]
ORDER BY [Open Notes] ASC

说明：因为你使用的是UNION而非UNION ALL，合并结果时已经自动对两个分支的重复记录做了去重，因此分组统计时不需要额外加DISTINCT，直接计数即可。查询返回的结果会自动按[Open Notes]升序排列，输出格式和你给出的示例完全一致。

内容的提问来源于stack exchange，提问作者kcarey