Good afternoon guys, I'm having a hard time doing a query.
Have the following table
Iwouldliketomakeaquerythatshowswhatandhowmanystatus_telephonyhadbyphoneanddate,thencreateatablewithallphonesthathadmorethan5OR+status"MACHINE" OR "INVALID NUMBER" and none of these phones had Status "RANDOM"
I find it a bit full but I really need your help
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 GROUP BY clause and the COUNT function.
Original: GROUP BY will condense into a single row all rows that share the same values for grouped expressions.
Free Translation: GROUP BY will combine in a single row all selected records that share the same values in grouped expressions.
Original: The COUNT function returns the number of input rows that match the specific condition of a query.
Free translation: The COUNT function returns the number of rows that match 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 to use the GROUP BY clause and the COUNT function, combined with NOT EXISTS to check that status_telefonia RANDOM exists for that number and HAVING clause to restrict 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 deletes a group of lines that does not satisfy the condition. HAVING is different from WHERE: WHERE filters individual lines before the GROUP BY application, while HAVING filters groups of lines created by GROUP BY.
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 if any rows will be returned. If you return at least one line, the result of EXISTS is "true"; if the subquery does not return rows, 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;