I am making a join between 2 tables, where they bring me number_phone that have a relationship and I bring the times that these are repeated, however, I am trying to make a condition to the count, so that it only lists those that are repeated more than 4 times, I tried with having account and it brings me the counter all in null. It is worth mentioning that I did not occupy group by for the count because it brought me wrong values.
SELECT
REPLACE(REPLACE(t.id_contrato,'0999',''),'0998','')as contrato ,
t.num_telefono,
conc.valor_actual,
(
SELECT COUNT('x')
FROM TBL_TELEFONO ct
WHERE ct.num_telefono = t.num_telefono
AND ct.origen_tel='TELEFONO CONTRATO'
-- HAVING COUNT(*) > 4
) as counter
FROM TBL_TELEFONO t
INNER JOIN CAM_TBL_ALERTA_CONCENTRADO conc ON t.num_telefono = conc.valor_actual
WHERE id_contrato IS NOT NULL
AND id_contrato NOT IN ('N/A')
ORDER BY 4 DESC
How can I list only those that are repeated more than 4 times?