Query for PSQL determine no.of patients in system current time

Question

select room_id,count (distinct(patient_id)) as patient_id 
from patient_flow where time='CURRENT_TIME';

I need the number of patients ids with corresponding room_ids at the current time.

 id | patient_id  | room_id |        time
----+-------------+---------+---------------------
  1 | 00035-67351 |       1 | 2015-06-09 10:11:20
  1 | 00035-67351 |       2 | 2015-06-09 10:31:20
  1 | 00035-67351 |       1 | 2015-06-09 10:12:20
  1 | 00035-67351 |       1 | 2015-06-09 10:12:40
  1 | 00035-67351 |       1 | 2015-06-09 10:15:40
  1 | 00035-67351 |       1 | 2015-06-09 10:30:40
  1 | 00035-67351 |       2 | 2015-06-09 10:32:40
  1 | 00035-67351 |       2 | 2015-06-09 10:36:40
  1 | 00035-67351 |       2 | 2015-06-09 10:38:40
  1 | 00035-67351 |       2 | 2015-06-09 10:50:40

I wrote the query above, but it does not execute.

Arulkumar · Answer 1 · 2015-08-24T10:15:54.453

0

You missed the GROUP BY when using with aggregate function COUNT(). The below query will work:

SELECT room_id, COUNT (DISTINCT(patient_id)) AS patient_id 
FROM patient_flow 
WHERE time = CURRENT_DATE
GROUP BY room_id;

edited Aug 24 '15 at 10:15

answered Aug 24 '15 at 08:36

Arulkumar

12,966
14
47
68

Thank you for your response.. I got ERROR: date/time value "current" is no longer supported. – Pari Venthan Aug 24 '15 at 09:50
Can you try, `CURRENT_DATE` or `CURRENT_TIMESTAMP` instead of `CURRENT_TIME` – Arulkumar Aug 24 '15 at 10:00
Can you try without `'` around the `CURRENT_DATE`? Check the updated answer. – Arulkumar Aug 24 '15 at 10:15

score 0 · Answer 2 · answered Aug 24 '15 at 10:19

0

select current_time, room_id, count (distinct(patient_id)) as patient_id from patient_flow  group by room_id;

I got result while executing above query..

answered Aug 24 '15 at 10:19

Pari Venthan

59
1
1
8

Query for PSQL determine no.of patients in system current time

2 Answers2