Compare values of the same field in a table

Question

I’m starting in SQL and appeared a problem that I’m not able to solve.

I have a table in which are recorded the payments of several customers, each customer made 6 payments. I need to know how many customers have made the 6 payments always at the same amount. Can anyone give me an idea of how I can count this?

In that case, customer 239 would go on count and 43 would not.

Answer 1

Buddy, you need to count the records by grouping them by person and value. Hence a clause applies HAVING to filter only counting results equal to 6.

SELECT cod_pessoa
  FROM pagamentos
 GROUP BY cod_pessoa, valor
HAVING COUNT(*) = 6

Remember that if the same person made 6 payments of R$10 and 6 more payments of $20, for example, it will appear in the result twice.

To avoid duplication use the DISTINCT in his select, thus:

SELECT DISTINCT cod_pessoa
  FROM pagamentos
 GROUP BY cod_pessoa, valor
HAVING COUNT(*) = 6

Answer 2

Value Suppose Your Table Is Payment Its Structure

create table Pagamentos(
       id int,
       cod_pessoa int,
       valor numeric(15,2)
)

To access how many people made 6 payments follow the query

select cod_pessoa
  from pagamentos
 group by cod_pessoa
having count(*) = 6