SQL query to simultaneously obtain group and individual values

Question

I am having trouble trying to determine the SQL query for this table:

There is a table of patients and their weight indications recorded in visits with the following columns:

• patient id
• weight reading
• visit identifier (one per visit)

In other words, if in two records the two visit identifiers are the same, then two weight readings were made on the same date of the visit.

I have this request to "get all patients with at least two weight readings above 150":

select patient_id 
  from patients 
 where weight_val > 50 
group by patient_id 
  having count(*) >= 2

Here is my problem: what if I want to modify this request so that I can request the following:

• "get all patients with indications of at least two values ​​above 150 at different visits"
• " 150 "

, "group by"? , ? , ( Oracle).

Answer 1

150

:

  SELECT p.patient_id 
    FROM PATIENTS p
   WHERE p.weight_val > 150 
GROUP BY p.patient_id 
  HAVING COUNT(DISTINCT p.visit_id) >= 2

150

:

  SELECT DISTINCT p.patient_id 
    FROM PATIENTS p
   WHERE p.weight_val > 150 
GROUP BY p.patient_id, p.visit_id
  HAVING COUNT(*) >= 2

Answer 2

:

1.

select patient_id 
  from patients 
 where weight_val > 150 
group by patient_id 
  having count(*) >= 2 and count(*) = count(distinct visit_id);

2.

select patient_id 
  from patients 
 where weight_val > 150 
group by patient_id 
  having count(*) >= 2 and count(distinct visit_id) = 1;