Count IF Mysql Workbench

Asked

Viewed 166 times

1

i have a table that has an ID field.

ID
1234
1234
1235
1235
1235
1236

And I would like to get via query the following result: 

ID  ID_Counter
1234    2
1234    2
1235    3
1235    3
1235    3
1236    1

Where in the second column the field value would be the amount of times the ID of that row appears in the ID column.

I tried the query: select ID, Count(ID) from tabela group by ID, but the result is:

ID  ID_Counter
1234    2
1235    3
1236    1

It would be like a cont.se() Microsoft Excel, but I’m not sure how to create the query in Mysql Workbench.

mysql mysql-workbench
  • 2

    But what is the logic of it? The result you get is the correct one!

  • The query is right... what exactly is missing?

  • Is that each line of this bank is an interaction of a consultant with the client, so not to lose accurate information that all lines appear and also need a counter for possible filters in reports.

2 answers

3

A Solution that solves but less elegant would be to do a subquery:

SELECT t.ID,sub.contador FROM tabela as t
 INNER JOIN (select ID, Count(ID) contador from tabela group by ID) as sub
 ON sub.ID = t.ID

It is a more immediate solution.

3


One of the solutions to your problem is using a Subquery to show the amount of records, in case it would look like this:

SELECT id, 
       (SELECT Count(id) 
        FROM   tabela t1 
        WHERE  t1.id = t2.id 
        GROUP  BY id) AS Count 
FROM   tabela t 
GROUP  BY id

Browser other questions tagged mysql mysql-workbench

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