Enrique Bravo Enrique Bravo - 23 days ago 6
SQL Question

SQL filtering, select specific values count when condition is met

Maybe I am just being dumb, but for some reason, this is working and yes, it works great but we wanted to add a condition with a sub table but maintainig same format.

BEGIN

SELECT
v.[id]
,v.[Vacante]
,v.[deptoId]
,v.[StatusId]
,v.[scholarYearId]
,v.[tipoVacanteId]
,v.[detalle]
,v.[createdDate]
,v.[createdBy]
,d.nombre as DeptoNombre
,s.nombre as statusNombre
,y.nombre as scholarYearNombre
,t.nombre as tipoVacanteNombre
,count(uv.id) as totalCandidatos
FROM
[dbo].[tbl_vacantes] v
LEFT JOIN
tbl_usuariosPorVacante uv on v.id = uv.vacanteId
--LEFT JOIN
-- dbo.[tbl_user] u on uv.userId=u.id
INNER JOIN
dbo.[tbl_depto] d ON d.Id = v.[deptoId]
INNER JOIN
dbo.[tbl_status] s ON s.Id = v.[statusId]
LEFT JOIN
tbl_scholarYear y ON v.scholarYearId=y.Id
LEFT JOIN
tbl_tipoVacante t ON v.tipoVacanteId=t.Id
--WHERE
-- u.progressId =3 OR u.progressId is null --Solo usuarios que ya temrinaron su proceso.
GROUP BY
v.[id]
,v.[Vacante]
,v.[deptoId]
,v.[StatusId]
,v.[scholarYearId]
,v.[tipoVacanteId]
,v.[detalle]
,v.[createdDate]
,v.[createdBy]
,d.nombre
,s.nombre
,y.nombre
,t.nombre
ORDER BY
v.id DESC


END

What we want to do, is in totalCandidatos (the count) keep a count, yes, but only count when dbo.[tbl_user] u has progressId = 3 and 4. Since now, it is counting all kind of progressId.

I know, it may be dumb. But Im stuck in this one.

Thanks!

Answer

You can use sum(case when <condition> then 1 else 0 end) to count the number of records returned that meet a certain criteria.