SQL query to get group by and distinct values at the same time

Question

I'm having trouble trying to define the SQL query for this table:

There's a table of patients and their weight readings recorded on visits with the following columns:

• patient ID
• weight reading
• visit ID (one per visit)

In other words, if in two records two visit IDs are the same, then two weight readings have been taken on that same visit date.

I have this query 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's my problem: What if I want to modify this query so that I can query the following:

1. "get all patients with at least two weight readings above 150 on different visits"
2. "get all patients with at least two weight readings above 150 on the same visit"

Is it possible to do it without removing the "group by" statement? if not, what is your recommended approach? I'm also open to adding a date column instead of visit ID if it makes it easier (i'm using Oracle).

Answer 1

Patients with at least two weight readings above 150 on different visits

Use:

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

Patients with at least two weight readings above 150 on the same visit

Use:

  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

try like this:

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;