How to count number of open jobs and closed jobs of specific job card in sql?

The requirement is to count the number of “open job” and the number of “closed jobs” of the “Technician” in the application.

if the field “status” is = “pending” or “work started” then the job is open, else the job status is closed

Im using a SQL script to count the number of open jobs and closed jobs, my problem is that I cant get it to count for a specific technician _id.

Here’s my SQL script for counting open jobs:

select count(*)
from jobcard
  left join "user" on "user"."_id_" = jobcard.technicians_fk
where jobcard.status = "Pending"

but the problem comes in where i want to only count for a specific user id

Answer

I would use conditional aggregation here:

SELECT
    u."_id_",
    COUNT(*) FILTER (WHERE jc.status IN ('pending', 'word started')) AS open_cnt,
    COUNT(*) FILTER (WHERE jc.status NOT IN ('pending', 'word started')) AS closed_cnt
FROM "user" u
LEFT JOIN jobcard jc
    ON u."_id_" = jc.technicians_fk
GROUP BY
    u."_id_";

I don’t know what Java has to do with this question, but you can’t use the exact syntax above from something like JPA or Hibernate. You might want to consider using a raw query for this particular problem.