Using Count with select all

Asked

Viewed 185 times

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.

  • select produto,matricula,data,fornecedor, count(*) from tb_teste 
 group by produto,matricula,data,fornecedor
 order by fornecedor

  • If it doesn’t matter the date and vendor then remove such fields from the query.

  • @anonymity of course that matters but I don’t want to group by those fields.

  • @Marconi exactly what I can’t do is put in the group by the date and supplier.

  • why do you need to data and fornecedor? conceptually it is not possible to count(*) without bringing the other fields of the select in grouping.

  • 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?

Show 1 more comment

1 answer

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

You are not signed in. Login or sign up in order to post.