Как подсчитать среднее количество записей за день в postgesql?

Question

[Вячеслав]

Хочу подсчитать количество записей всего на одного пациента и среднее количество записей за сутки:

написал такой запрос:

select 
patient_id, 
count(id) as count_measure, 
avg(select count(1) from monitoring_pulse mp2 where mp2.patient_id = mp1.patient_id and taken_at > '2024-09-01' group by to_char(mp2.taken_at, 'DD.MM.YYYY')) as avg_count_measure 
from monitoring_pulse mp1
where patient_id in (189, 1323, 959)
and taken_at > '2024-09-01'
GROUP BY patient_id

Ошибка ERROR: syntax error at or near "select"
LINE 4: avg(select count(1) from monitoring_pulse mp2 where mp2.pati...

как исправить?

Comments

[Михаил Р.]

Разве запрос внутри avg поддерживается в postrgresql?

Answer 1

[Вячеслав]

Так сработало:

select 
mp1.patient_id, 
count(id) as count_measure, 
(select avg(scr.count_records) from (select count(1) as count_records from monitoring_pulse mp2 where mp2.patient_id = mp1.patient_id and taken_at > '2024-09-01' group by to_char(mp2.taken_at, 'DD.MM.YYYY')) as scr) as avg_count_measure 
from monitoring_pulse mp1
where mp1.patient_id in (189, 1323, 959)
and mp1.taken_at > '2024-09-01'
GROUP BY mp1.patient_id