sql - postgres - select one specfic row of another table and store it as column

Question

I have a table for timeTracks with the properties startTime and stopTime that are recorded for another table called project.

With the following aggregates I managed to display the count and sum of the amount of tracks that belongs to a project. In case an active Track (one without stopTime) exists, I want 2 additional columns named "activeTrackId" and "activeTrackStartTime".

SLECT
count(time_track."timeTrackId") AS "timeTracksTotalCount",
floor(date_part('epoch'::text, sum(time_track."stopTime" - time_track."startTime")))::integer AS "timeTracksTotalDurationInSeconds"
activeTimeTrackId ??
activeTimeTrackStartTime ??
...
FROM project
LEFT JOIN time_track on time_track."fkProjectId" = project."projectId"

Technically there can be only one active track at a time. However just in case, it should only select the latest active track, if there a two tracks without stopTime.

How can I that in postgres?

Answer 1

You can use aggregation functions:

SELECT count(t."timeTrackId") AS "timeTracksTotalCount",
       floor(date_part('epoch'::text, sum(t."stopTime" - t."startTime")))::integer AS "timeTracksTotalDurationInSeconds"
       (array_agg(t."timeTrackId" order by t."startTime" desc) filter (where t."stopTime" is null))[1] as activeTimeTrackId,
       max(t."startTime") filter (where t."stopTime" is null) as activeTimeTrackStartTime 
...
FROM project p LEFT JOIN
     time_track t
     ON t."fkProjectId" = p."projectId"
GROUP BY ?;

Note that Postgres doesn't offer a first() aggregation function, so this uses array aggregation and then takes the first element.

If you are using an older version of Postgres, filter may not be available. You can replace that with case expressions.