Using Count with select all

Question

Using Count with select all

Navigation

#1 by (1 votes)

0

I want to make a sql by counting the records with duplicity:

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

In this form of the error, sql requests the fields data,fornecedor but I can not group by them either.

sql firebird

asked by anonymous 14.12.2018 / 18:33

1 answer

Return value of an input Webservice does not accept xml file

score 1 · Accepted Answer

You can always try as follows:

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 will only have the link for the product and registration, I do not know if it will 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 GROUP BY otherwise you will not be able to execute the query .