SQL Count(*) and Query a UNION query

I am a novice to SQL and seeking a little direction. The below query correctly identifies the number of rows (count(*)) in the combined (Union) query. However, in addition to the number (count), I need to also display the Name (cn1.FullName) as there are multiple users. I have looked high and low for a similar example without success. I am not able to determine how to list the cn1.FullName within the query. I’m seeking a layout as follows –

[Open Notes] | [User]

13       | User1
21       | User2
32       | User3

and sorted by [Open Notes] asc

Any assistance would be greatly appreciated.

Select COUNT(*) as [Open Notes] from 
(
SELECT distinct(pe.EncounterID) AS [Encounter ID], 
    cn1.FullName AS [Name],
    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 [Provider Name],
    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

Thanks in advance,

Kcarey

Answer

Try:

Select [Name], COUNT(*) as [Open Notes] from 
( union'd queries)
group by [Name]

Advertisement

Answer