1

i have the following tables: VISITS: vid, pid, date PATIENT: pid, pname, age, gender . what i want is to get the patient with more visits by using a count and a max function. i did the count with this query:

select Patient.pname, count (Visits.pid) as PatientsVisits from ( Patient inner join Visits on Patient.pid = Visits.pid) group by Patient.pname

So my output displays the patient's name and the number of visits each one has.

So how can i get the max value ?? ps: im using sql server

appleduardo
  • 59
  • 2
  • 10

2 Answers2

3

SELECT returns a table, so use a nested call to select the row with the maximum value from that table.

SELECT DISTINCT pname, 
       COUNT(vid) AS PatientVisits 
FROM Visits 
JOIN Patients ON Visits.pid = Patients.pid
GROUP BY Visits.pid 
HAVING COUNT(vid) = (
    SELECT MAX(y.x) 
    FROM (
       SELECT COUNT(vid) AS x 
       FROM Visits 
       GROUP BY pid) AS y
);

Basically we use one SELECT to get the COUNT of visits GROUPED BY*patient, then we nest that inside a second SELECT to get the MAX number of visits, then we nest that inside a third SELECT to find the patient HAVING that number of visits.

Alberto Moro
  • 1,014
  • 11
  • 22
BonzaiThePenguin
  • 1,413
  • 13
  • 19
  • wow that looks great! but what is "Counts" ? i just typed this on sql and i get and error on Max(Counts.pid) says the coloumn name isnt valid. And at the end of the query ("AS Counts") another error appears: no column was specified to the 2nd column of "Counts" ... – appleduardo May 17 '14 at 01:37
  • The "AS Counts" part is exactly like how "AS PatientVisits" renames the column, except this renames the result table. I tested the call on one of those online SQL quizzes and modified it to work here, but it looks like I made a mistake in the conversion. :( – BonzaiThePenguin May 17 '14 at 01:42
  • Maybe this will work? SELECT Counts.pname, MAX(Counts.pid) FROM (SELECT Patient.pname AS pname, COUNT(Visits.pid) AS pid FROM (Patient INNER JOIN Visits ON Patient.pid = Visits.pid) GROUP BY Patient.pname) AS Counts; – BonzaiThePenguin May 17 '14 at 01:43
  • but at the time when you type: Counts.pname, that reffers to the column name as well? or to the Patient's name? – appleduardo May 17 '14 at 01:47
  • Counts.pname refers to the pname column of the Counts table, even if the Counts table does not exist until "later" in the call. These are equivalent: "SELECT table.column FROM table" and "SELECT x.column FROM table AS x" – BonzaiThePenguin May 17 '14 at 01:49
  • And both are equivalent to this, which is only one step away from the full call: "SELECT x.column FROM (SELECT * FROM table) AS x" – BonzaiThePenguin May 17 '14 at 01:53
  • still getting errors: the identifier formed by various parts "Counts.pname" couldnt link up and the same to Counts.pid :/ – appleduardo May 17 '14 at 01:59
  • What's the exact error message? – BonzaiThePenguin May 17 '14 at 02:00
  • Just to show where I'm coming from, I'm literally just typing stuff into the first question on this web page: http://sqlzoo.net/wiki/The_JOIN_operation. When I changed the names of the tables and columns to match the ones on that page, it worked correctly. – BonzaiThePenguin May 17 '14 at 02:00
  • But it's possible they're using a slightly different dialect of SQL than the one you're using. – BonzaiThePenguin May 17 '14 at 02:01
  • this is the complete error's sentence: The 'Counts.pname' column selection list is invalid because it is not contained in an aggregate function or the GROUP BY clause. – appleduardo May 17 '14 at 02:04
  • Hm, that's definitely a different dialect of SQL. Also I noticed my original answer was wrong. Looking into it now... – BonzaiThePenguin May 17 '14 at 02:24
  • YEEP, so now i typed group by Counts.pname at the end of the query but it shows me all the patients with the visits that each on has, so no max value is shown in there :/ – appleduardo May 17 '14 at 02:33
  • This seems to work: http://sqlfiddle.com/#!2/fe9bf5/18 – BonzaiThePenguin May 17 '14 at 02:45
0

As I understand your problem - you want the top 1 patient with the most visits and this should give it to you

select top 1 Patient.pname, count (Visits.pid) as PatientsVisits 
from Patient inner join Visits on Patient.pid = Visits.pid 
group by Patient.pname
order by count(Visits.pid) desc
attila
  • 2,219
  • 1
  • 11
  • 15