CODEWITHSUNDEEP
sql
S.John
S.John

Reputation: 23

SQL Query to find the most frequent element in relation to an other element in a table

I have a hospital database which contains the tables:

equipment

id CHAR(40) NOT NULL, model CHAR(20) NOT NULL, serialno VARCHAR(20) NOT NULL, release_date DATE, PRIMARY KEY (id)

and

appointmentrelations
appointment_id CHAR(40) NOT NULL, 
 medicine_id    CHAR(40) NOT NULL, 
 equipment_id   CHAR(40) NOT NULL,
 PRIMARY KEY (appointment_id, medicine_id, equipment_id), 
 FOREIGN KEY (appointment_id) REFERENCES appointment(id), 
 FOREIGN KEY (medicine_id)REFERENCES medicineprice(medicine_id), 
 FOREIGN KEY (equipment_id) REFERENCES equipment(id)

and other some other tables.

Table appointmetrelations describes the appointments of the hospital and relates an appoointment with the equipments and the medicines that where used in it.

I want to write a query which find the equipment which was used the most in relation to his age.

appointmentrelations
a1    m1    e1  
a2    m2    e2
a3    m3    e3
a4    m3    e3
a5    m4    e2

equipment
e1  model1  se1 2004-07-14
e2  model2  se2 2017-10-19
e3  model3  se3 2015-01-08

In the above sample the equipment that were used the most in relation to his age is e2.

I tried the following:

SELECT equipment_id, COUNT(DISTINCT appointment_id) AS ab
FROM appointmentrelations
GROUP BY equipment_id
ORDER BY ab DESC
LIMIT 1

to count the number of different appointments that each equipment were used in but I dont know how to relate it with the equipment age.

Upvotes: 1

Views: 124

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133400

You could use an inner join for obtain all the result order by usage 

  select e.id, count(DISTINCT a.appointment_id), e.model, e.date 
  from equipment e 
  inner join appointmentrelations a on a.appointment_id = e.id
  group by e.id , e.model, e.date 
  order by count(DISTINCT a.appointment_id) DESC

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

If i understand correctly, you can wrap your current query in subquery: 

SELECT * 
FROM equipment e
WHERE id  = (SELECT equipment_id
             FROM appointmentrelations
             GROUP BY equipment_id
             ORDER BY COUNT(DISTINCT appointment_id) DESC
             LIMIT 1);

Upvotes: 1

Related Questions