0
I am wanting to make an sql counting the records with duplicity:
select produto,matricula,data,fornecedor, count(*) from tb_teste
group by produto,matricula
order by fornecedor
This way of the error, sql requests the fields data,fornecedor but I can’t group for them either.
1
You can always try it the way below:
SELECT TT.produto
, TT.matricula
, TT.data
, TT.fornecedor
, TT.contador
FROM tb_teste TT
INNER JOIN (
SELECT COUNT(1) AS contador
, produto
, matricula
FROM tb_teste
GROUP BY produto
, matricula
) TT2 ON TT2.produto = TT.produto
AND TT2.matricula = TT.matricula
WHERE TT2.contador > 1
ORDER BY TT.fornecedor
But then you’ll only have the link by product and license plate, I don’t know if it’ll be enough.
The way I wanted to do it initially is impossible. If you want to count the records based on certain fields, they all have to be in the GROUP BY otherwise you won’t be able to execute the query.
if I want to bring the total(Count) of duplicate records, in this same query.
In this case just put a COUNT(1) in the SELECT and remove all other fields. ORDER BY will also be unnecessary and can be removed. If the answer is correct or useful, mark it :)
Browser other questions tagged sql firebird
You are not signed in. Login or sign up in order to post.
select produto,matricula,data,fornecedor, count(*) from tb_teste 
 group by produto,matricula,data,fornecedor
 order by fornecedor– Marconi
If it doesn’t matter the date and vendor then remove such fields from the query.
– anonimo
@anonymity of course that matters but I don’t want to group by those fields.
– Tiago Casanova
@Marconi exactly what I can’t do is put in the group by the date and supplier.
– Tiago Casanova
why do you need to
dataandfornecedor? conceptually it is not possible tocount(*)without bringing the other fields of the select in grouping.– rLinhares
If for each product and registration there can be more than one date and/or supplier, and you do not want to consider them, then which one you will draw to display?
– anonimo