How do I convert this MySQL query to MSSQL query COUNT()

Question

I need help. how can I convert this MySQL query count into MsSQL query

SELECT patientmasters.ID,
patientmasters.HRN,
patientmasters.LastName,
patientmasters.FirstName,
patientmasters.MiddleName,
 count(casehistories.PatientID) as NumberOfRecords        
from patientmasters
left join casehistories
on (patientmasters.ID = casehistories.PatientID)
group by
    patientmasters.ID;

This works in MySql, but it wasn't especially good practice. — Joel Coehoorn, Feb 21 '20 at 05:51

score 1 · Accepted Answer · answered Feb 21 '20 at 05:14

This answer could be useful. For MS SQL Server it should be:

SELECT patientmasters.ID,
patientmasters.HRN,
patientmasters.LastName,
patientmasters.FirstName,
patientmasters.MiddleName,
count(casehistories.PatientID) as NumberOfRecords        
from patientmasters
left join casehistories
on (patientmasters.ID = casehistories.PatientID)
group by
    patientmasters.ID,
    patientmasters.HRN,
    patientmasters.LastName,
    patientmasters.FirstName,
    patientmasters.MiddleName;

score 0 · Answer 2 · answered Feb 21 '20 at 12:56

In either database, I would recommend a correlated subquery:

select pm.*,
       (select count(*)
        from casehistories ch
        where pm.ID = ch.PatientID
       ) as NumberOfRecords        
from patientmasters pm;

In particular, both databases will take advantage of an index on casehistories(PatientID). This should have better performance by avoiding the outer aggregation.

How do I convert this MySQL query to MSSQL query COUNT()

2 Answers2