Search code examples
mysqlselect

MySQL SELECT com COUNT

I have the following SELECT:

SELECT NrCertificadoPesagem, CodArquivoUsina, Safra, Arquivo 
FROM t413 
WHERE NrCertificadoPesagem<>"00000000" 
GROUP BY NrCertificadoPesagem, CodArquivoUsina, Safra, Arquivo 
ORDER BY NrCertificadoPesagem ASC, CodArquivoUsina ASC, Safra ASC;

... result:

enter image description here

a) I need to include a column "CertificadosQty" which is the count of how many times the certificate is repeated in this same list;

b) Also display only the result of "CertificadosQty" > 1 (greater than 1), that is, the list will look something like this

enter image description here

I even managed to achieve this result, but to do so I had to make 4 selects one within the other, that is, I must have done it the hard way.

I'm already saying thank you to the class :)

Solution

  • You can run this, too

    SELECT
  NrCertificadoPesagem,
  COUNT(*) AS CertificadosQty,
  CodArquivoUsina,
  Safra,
  Arquivo
FROM
  t413 
WHERE
  NrCertificadoPesagem <> "00000000" 
GROUP BY
  NrCertificadoPesagem, CodArquivoUsina, Safra, Arquivo 
HAVING
  COUNT(*) > 1 
ORDER BY
  NrCertificadoPesagem ASC, CodArquivoUsina ASC, Safra ASC;

    GROUP BY - is to group on each records