CODEWITHSUNDEEP
sql-server
Guy
Guy

Reputation: 43

Counting unique values in SQL

I'm having trouble with an SQL question. I have two tables: personals and patient treatments. The personal table has the the information of the personals, and the treatments table has the personal ID and the patient ID who received the treatment. I need to find the personal who gave the most treatments to different patients. I have tried the following:

   SELECT ID , First_Name , Last_Name
   FROM Personal JOIN Patients_Treatments ON ID = Personal_id 
   GROUP BY ID , First_Name , Last_Name
   HAVING COUNT(*) >= ALL (SELECT COUNT(Personal_id) FROM Patients_Treatments 
   GROUP BY Personal_id)

The problem is that it gives the personal who gave the most treatments, not the one who gave the most treatments to different patients. Thank you for your time !

EDIT: Found a solution if anyone is interested:

SELECT Personal.ID,First_Name,Last_Name
FROM Personal,Patients_Treatments
WHERE Personal.id = Patients_Treatments.Personal_id
GROUP BY Personal.ID,First_Name,Last_Name
HAVING COUNT(distinct Patient_ID) >= all(SELECT COUNT(distinct Patient_ID) 
FROM [Patients_Treatments] GROUP BY Personal_id)

Upvotes: 2

Views: 70

Answers (1)

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

This will return the personal(s) with highest treatments.

SELECT TOP 1 WITH TIES ID , First_Name , Last_Name
FROM Personal JOIN Patients_Treatments ON ID = Personal_id 
GROUP BY ID , First_Name , Last_Name
ORDER BY COUNT(DISTINCT Patient_ID) DESC

Upvotes: 3

Related Questions