CODEWITHSUNDEEP
sqloracle-databaseviewcountmax
Shandep
Shandep

Reputation: 147

Getting max count from a view

I created a view that allows me to view the number of appointments a doctor has.

view code: 

create view DocApps as
select d.doctorid, d.surname ||','|| d.given as "Doctor", count(*) as "Appointments"
from doctor d, appointment
where d.doctorid=appointment.doctorid
group by d.doctorid, d.surname, d.given;

and I now need to show the doctor with the most appointments, and I have used this code, as it has worked in previous queries: 

select Doctor, Appointments
from docapps
where Appointments in (select max(Appointments)
from docapps);

but it is returning with "Appointments" : invalid identifier, when I have specified that it is that?

Upvotes: 1

Views: 44

Answers (1)

Mureinik
Mureinik

Reputation: 311853

Using double quotes (") would make object names such as tables and columns case-sensitive, which is what leads to your problem. Just drop them from the view declaration and you should be fine:

CREATE VIEW DocApps AS
SELECT   d.doctorid, d.surname ||','|| d.given AS Doctor, 
         COUNT(*) AS Appointments
FROM     doctor d, appointment
WHERE    d.doctorid=appointment.doctorid
GROUP BY d.doctorid, d.surname, d.given;

Upvotes: 1

Related Questions