Skip to content
Advertisement

How to achieve conditional array aggregate?

For simplicity I’ll use the following patient and appointment tables.

What I want is one report that contains the following data.

So what I need to do is:

  1. Get the patient names from patient table
  2. aggregate all appointment statuses of the patient
  3. aggregate all appointment times of appointments that have status=’active’

So the initial query that I created is:

DBFiddle

However the above satisfies the first two requirements, however for the third one it’s aggregating all scheduled times and not just those that have an active status.

It would be nice if array_agg(field WHERE ) works as shown below:

But the above does not work. So is there another simple way of achieving this?

Advertisement

Answer

You are looking for the filter() option:

User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement