counting duplicate rows in mysql

Question

I know this topic has been discussed million times, but I am having a strange output. I am trying to accomplish a job where a sql query will count the number of duplicate + non-duplicate rows. Basically I have the following table:

My query is

select id, count(*) as total from master_huts group by id

And I am getting this

ID     Name
0      (some weird number)
501    3
502    1
865    2

It's pretty straight forward, but not sure where I am wrong.

Table structure

CREATE TABLE `master_huts` (
 `hutids` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

@user3671491 how is defined your id column ? int or varchar ? — echo_Me
– echo_Me, Commented Jun 19, 2014 at 12:47
you sure you dont have 0 in your table or someother values ? look demo here it works fine sqlfiddle.com/#!2/f7195/1 — echo_Me
– echo_Me, Commented Jun 19, 2014 at 12:50
if you have large table ,try do this to see if you have 0 value select id from yourtable where id = 0 — echo_Me
– echo_Me, Commented Jun 19, 2014 at 12:54

ka_lin · Accepted Answer · 2014-06-19 12:44:23Z

1

Do the following:

select count(id) as ocurences, id from master_huts group by id

answered Jun 19, 2014 at 12:44

ka_lin

9,4226 gold badges39 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ka_lin Over a year ago

It means you really have a row with id=0

user3671491 Over a year ago

My mistake as usual. Thanks mate.

K.K.Agarwal · Accepted Answer · 2014-06-19 12:44:08Z

1

Just use id in count instead of *

like this...

select id, count(id) as total from whatever group by id

answered Jun 19, 2014 at 12:44

K.K.Agarwal

8565 silver badges10 bronze badges

3 Comments

user3671491 Over a year ago

Did that but still I am getting a 0 as first row

K.K.Agarwal Over a year ago

Are you sure your table has exact the same data given by you?

user3671491 Over a year ago

Solved :). my mistake. Sorry

deviloper · Accepted Answer · 2014-06-19 12:54:36Z

1

try this and see if you still get anything weird:

select id, count(*) as total from master_huts where id != 0 group by id;

answered Jun 19, 2014 at 12:54

deviloper

7,25210 gold badges52 silver badges79 bronze badges

Collectives™ on Stack Overflow

counting duplicate rows in mysql

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related