Count IF MySQL Workbench

Question

Count IF MySQL Workbench

Navigation

#1 by (3 votes)
#2 by (3 votes)

1

I have a table that has an ID field.

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

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 number 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 type cont.se() of Microsoft Excel, but I'm not sure how to create the query in the MySQL Workbench.

mysql mysql-workbench

asked by anonymous 02.02.2017 / 12:50

2 answers

3

A solution that solves, but less elegant would be to make 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's a more immediate solution.

02.02.2017 / 13:00

Call another Layout Images in html or via css

score 3 · Accepted Answer

One of the solutions to your problem is to use a SubQuery to show the number of records, in case it looks 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