Query to separate records

Question

Good afternoon guys, I’m having trouble performing a query.

Have the following table

I would like to make a query that shows which and how many status_telefonia had by phone and date, then create a table with all phones that had more than 5 OR + status "MACHINE" OR "INVALID NUMBER" and none of these phones had the status "RANDOM"

I think it’s a little complete but I really need your help

Answer 1

For the first part, as you said yourself, you want to group and count events by phone and date. For this we will use the clause GROUP BY and the function COUNT.

---

GROUP BY

Original: GROUP BY will condense into a single Row all Selected Rows that share the same values for the grouped Expressions.

Free translation: GROUP BY will combine in a single row all selected records that share the same values in the grouped expressions.

---

COUNT

Original: The COUNT Function Returns the number of input Rows that match a specific condition of a query.

Free translation: The COUNT function returns the number of rows that matches the condition specified in the query.

---

SELECT telefone,
       data_registro,
       COUNT(1) as quantidade
  FROM tabela t
 GROUP BY telefone, data_registro;

For the second part we will continue using the clause GROUP BY and the function COUNT, allied with the NOT EXISTS to check if there is no status_telefonia RANDOM for that number and the clause HAVING to restrict the results to those who have 5 or more occurrences.

---

HAVING

Original: HAVING eliminates group Rows that do not satisfy the condition. HAVING is Different from WHERE: WHERE Filters individual Rows before the application of GROUP BY, while HAVING Filters group Rows created by GROUP BY.

Free translation: HAVING eliminates a group of lines that does not satisfy the condition. HAVING is different from WHERE: WHERE filters individual lines before the application of GROUP BY, while HAVING filters groups of lines created by GROUP BY.

---

EXISTS

Original: The argument of EXISTS is an arbitrary SELECT statement, or subquery. The subquery is evaluated to determine whether it Returns any Rows. If it Returns at least one Row, the result of EXISTS is "true"; if the subquery Returns no Rows, the result of EXISTS is "false".

Free translation: The EXISTS argument is an arbitrary SELECT statement, or subquery. The subquery is evaluated to determine whether a line will be returned. If you return at least one line, the result of EXISTS is "true"; if the subquery does not return lines, the result of EXISTS is "false".

---

SELECT telefone,
       COUNT(1) as quantidade
  FROM tabela t
 WHERE status_telefonia IN ('MACHINE', 'INVALID NUMBER')
   AND NOT EXISTS(SELECT 1
                    FROM tabela t2
                   WHERE t2.telefone = t.telefone
                     AND status_telefonia = 'RANDOM')
 GROUP BY telefone
 HAVING count(1) >= 5;