I'm starting in SQL and there was a problem that I can not resolve.
I have a table on which are registered payments from multiple customers, each customer made 6 payments. I need to know how many customers made the 6 payments ever with the same value. Can anyone give me an idea how I can tell this?
Home
In this case the client 239 would enter the count and the 43 would not.
Friend, you need to count the records by grouping them by person and value . Then you apply a HAVING clause to filter only the results of the count equal to 6.
SELECT cod_pessoa
FROM pagamentos
GROUP BY cod_pessoa, valor
HAVING COUNT(*) = 6
Remembering that if the same person made 6 payments of $ 10 and another 6 payments of $ 20, for example, it will appear in the result twice.
To avoid duplicity, use DISTINCT in your select , like this:
SELECT DISTINCT cod_pessoa
FROM pagamentos
GROUP BY cod_pessoa, valor
HAVING COUNT(*) = 6
Assume that your table is pay its structure is
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