Count equal records with SELECT DISTINCT and COUNT

Asked

Viewed 57,768 times

4

How do I count the equal results and add the amount in front?

For example:

Currently the records of my table are like this:

Id  | Nome 
------------
0   | Teste
1   | Teste
2   | Teste

I want to give a DISTINCT in them and leave them like this:

Id  | Nome 
------------
0   | Teste(3)

SQLFIDDLE for testing

I’m using MySql!

mysql sqlfiddle

5 answers

10


You can try using the following sql expression:

select  distinct id, CONCAT(nome, "(",count(id),")") as Nome from teste group by nome;

  • The same works for MySql? That was the tag which I inserted in the question.

  • 1

    works @Exception parabens Pedro

  • @Exception works yes tested here in Mysql Workbench

  • @Otto thank you^^

  • 4

    No need to use DISTINCT...

  • @Kaduamaral yes but he asked... Count equal records with SELECT DISTINCT and COUNT

  • @Pedrorangel I tested here and it worked. Thanks!

Show 2 more comments

3

I sought the same help and this was a solution that worked.

SELECT DISTINCT campo1
               ,campo2
               ,campo3
               ,campo4
               ,campo5
               ,campo6
               ,campo7
               ,COUNT(*) AS quantidade
  FROM tabela_a
 GROUP BY campo1
         ,campo2
         ,campo3
         ,campo4
         ,campo5
         ,campo6
         ,campo7
 ORDER BY quantidade DESC

Credits: Luan Moreno [SQL Soul]|| SQL Server Specialist || MCTS SQL Server Admin and Dev @luansql

  • 2

    Does anyone explain the negative votes? The principle is right, it’s just not chewed up for the specific problem. The query is not explained in detail, but the same thing with the accepted question, which is not negative.

3

See if this helps you:

select COUNT(distinct Nome) from @TableTeste
  • 1

    Not because you only counted the rows, did not put the Teste(3) for example. You should count the rows and put in front of the result.

  • yes yes I realized, had forgotten to put the Concat as the friend @Pedro Rangel

2

In the SQLFIDDLE Test: 

create table algo(
  id int not null auto_increment,
  nome varchar(10) not null,
  primary key(id));


  insert into algo (id, nome) values (0,'Teste');
  insert into algo (nome) values (1,'Teste');
  insert into algo (nome) values (2,'Teste');

  select distinct concat(nome,'(',(select count(id) from algo),')') as nome from algo;
  select distinct concat(nome,'(',count(id),')') as nome from algo;
  select distinct sum(id-id) as id, concat(nome,'(',count(id),')') as nome from algo group by nome;

-2

I think this one might help you!!

SELECT   Nome,
         COUNT(Nome) AS Qtd
FROM  Clientes
GROUP BY Nome
ORDER BY COUNT(Nome) DESC

Browser other questions tagged mysql sqlfiddle

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