UNIQUE records - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

UNIQUE records

Author

Topic

robson
Starting Member

22 Posts

Posted - 2008-05-12 : 05:52:59

I have the following sql:

SELECT COUNT(patient.patientID) AS total_patients
FROM patient
LEFT JOIN patient_record ON patient_record.patientID = patient.patientID
WHERE sub_categoryID = 4 OR patient_record.allocated = 4

from the database this gives me a COUNT of 22, it should only be 10. I am doing a join and it gives the total records in the two tables where I only want the total in the left table(patient).
How can I GROUP BY patient.patientID in a COUNT query

Thanx, Robson

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-12 : 05:56:22

Change like this and try:-
SELECT COUNT(DISTINCT patient.patientID) AS total_patients
FROM patient
LEFT JOIN patient_record ON patient_record.patientID = patient.patientID
WHERE sub_categoryID = 4 OR patient_record.allocated = 4

robson
Starting Member

22 Posts

Posted - 2008-05-12 : 06:00:38

Thank you visakh16.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources