How to use the function count in this query in postgres

Question

I need to count "Estado de solicitud" for each "Tecnico" i mean, for each "Tecnico" how many "Abiertos" or "En espera" I have

This is what I have

SELECT ti.FIRST_NAME AS "Técnico", std.STATUSNAME AS "Estado de solicitud"  
FROM WorkOrder wo 
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID 
LEFT JOIN SDUser td ON wos.OWNERID=td.USERID 
LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID 
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID 
WHERE ( std.STATUSID != 3  ) AND (std.STATUSID != 4)

This is the result for the query

And something like this is that I need

Gordon Linoff · Accepted Answer · 2019-06-07 17:23:40Z

2

You can do this with conditional aggregation:

SELECT ti.FIRST_NAME AS "Técnico",
       COUNT(*) FILTER (WHERE std.STATUSNAME = 'Abierto') as abiertos,
       COUNT(*) FILTER (WHERE std.STATUSNAME = 'En Espera') as en_esperas
FROM WorkOrder wo LEFT JOIN
     WorkOrderStates wos
     ON wo.WORKORDERID = wos.WORKORDERID LEFT JOIN
     SDUser td
     ON wos.OWNERID = td.USERID LEFT JOIN
     AaaUser ti
     ON td.USERID = ti.USER_ID LEFT JOIN
     StatusDefinition std
     ON wos.STATUSID = std.STATUSID 
WHERE std.STATUSID NOT IN (3, 4)
GROUP BY ti.FIRST_NAME

answered Jun 7, 2019 at 17:23

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to use the function count in this query in postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related