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?

CodePudding user response：

I don't like debugging code with no sample data, so I'll try to illustrate it on Scott's sample EMP table. "Jobs" will act like your "telephone numbers".

SQL> select deptno, ename, job
  2  from emp
  3  order by job;

    DEPTNO ENAME      JOB
---------- ---------- ---------
        20 SCOTT      ANALYST    --> 2 analysts
        20 FORD       ANALYST
        10 MILLER     CLERK      --> 4 clerks
        30 JAMES      CLERK
        20 SMITH      CLERK
        20 ADAMS      CLERK
        30 BLAKE      MANAGER    --> 3 managers
        20 JONES      MANAGER
        10 CLARK      MANAGER
        10 KING       PRESIDENT  --> 1 president
        30 TURNER     SALESMAN   --> 4 salesmen
        30 MARTIN     SALESMAN
        30 WARD       SALESMAN
        30 ALLEN      SALESMAN

14 rows selected.

SQL>

According to that, we'd like to fetch all clerks and salesmen as there are 4 (or more) of them.

Instead of count aggregate function, use count in its analytic form:

SQL> select deptno, ename, job,
  2    count(*) over (partition by job) cnt
  3  from emp
  4  order by job;

    DEPTNO ENAME      JOB              CNT
---------- ---------- --------- ----------
        20 SCOTT      ANALYST            2
        20 FORD       ANALYST            2
        10 MILLER     CLERK              4
        30 JAMES      CLERK              4
        20 SMITH      CLERK              4
        20 ADAMS      CLERK              4
        30 BLAKE      MANAGER            3
        20 JONES      MANAGER            3
        10 CLARK      MANAGER            3
        10 KING       PRESIDENT          1
        30 TURNER     SALESMAN           4
        30 MARTIN     SALESMAN           4
        30 WARD       SALESMAN           4
        30 ALLEN      SALESMAN           4

14 rows selected.

SQL>

Now things become easier: use that query as a CTE (or a subquery), and apply where clause:

SQL> with temp as
  2    (select deptno, ename, job,
  3       count(*) over (partition by job) cnt
  4     from emp
  5    )
  6  select deptno, ename, job
  7  from temp
  8  where cnt >= 4
  9  order by job;

    DEPTNO ENAME      JOB
---------- ---------- ---------
        10 MILLER     CLERK
        30 JAMES      CLERK
        20 SMITH      CLERK
        20 ADAMS      CLERK
        30 TURNER     SALESMAN
        30 MARTIN     SALESMAN
        30 WARD       SALESMAN
        30 ALLEN      SALESMAN

8 rows selected.

SQL>

Applied to your query (again, can't test it without any sample data):

with temp as
  (select
     replace(replace(t.id_contrato,'0999',''),'0998','')as contrato , 
     t.num_telefono,
     conc.valor_actual,
     count(*) over (partition by t.num_telefono) 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')
  )
select contrato, num_telefono, valor_actual
from temp
where counter >= 4;

CodePudding user response：

Wrap the query in another to return only where count > 4

select *
from (
    <your query, but without order by>
) x
where count > 4
order by count desc

CodePudding user response：

Join to the selection that have more than 4

SELECT
    REPLACE(REPLACE(t.id_contrato,'0999',''),'0998','')as contrato , 
    t.num_telefono,
    conc.valor_actual,
    ct.counter 
FROM TBL_TELEFONO t
INNER JOIN (
    SELECT num_telefono, COUNT(*) AS counter
    FROM TBL_TELEFONO  
    WHERE origen_tel='TELEFONO CONTRATO' 
    GROUP BY num_telefono
    HAVING COUNT(*) > 4 
) ct 
   ON ct.num_telefono = t.num_telefono
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 ct.counter DESC