Trouble with COUNT

0
votes

I have two tables, PATIENT and VISIT. One with PatientID as the primary key and one with VisitID as the primary key. I need to select the first name and last name of the patients that have visited the hospital more than twice.

I have tried DISTINCT, a nested where clause, INNER JOIN, etc. 

SELECT FirstName
     , LastName
     , PatientID
     , COUNT(*) AS total_visits 
  FROM VISIT 
 WHERE total_visits > 2;

It should just show the first and last name of the patients that have more than two occurrences in the VISIT table, but no matter how I rearrange the code it doesn't work.

2
sqlms-access

2 Answers

0
votes

Following on from Gordon's answer and your comment I presume that PatientID in VISIT is a key to the PATIENT table. So you will need to use an ´INNER JOIN´. So your query looks something like this:

SELECT FirstName, LastName, v.PatientID, COUNT(*) AS total_visits
FROM VISIT v
INNER JOIN PATIENT p ON p.PatientID = v.PatientID
GROUP BY FirstName, LastName, v.PatientID
HAVING COUNT(*) > 2;

Note that AFAIK in Access you cannot use the alias name in the HAVING clause. You need to repeat the COUNT(*) as is.

0
votes

You need GROUP BY and HAVING:

SELECT FirstName, LastName, PatientID, COUNT(*) AS total_visits
FROM VISIT
GROUP BY FirstName, LastName, PatientID
HAVING total_visits > 2;