MYSQL COUNT return duplicate rows

Question

Is there any way to use COUNT and return with a COUNT column for each row. If i were to use GROUP BY it would only return 1 row for each value. I want it to show duplicates.

Example Data:

City
Seattle
Dallas
Seattle
New York
Boston
Boston
Portland

I would like to return:
Seattle 2
Dallas 1
Seattle 2
New York 1
Boston 2
Boston 2
Portland 1

John Woo · Accepted Answer · 2013-07-26 19:57:28Z

3

you can use correlated subquery.

SELECT  a.City,
        (SELECT COUNT(*) FROM tableName b WHERE a.City = b.City) totalCount
FROM    tableName a

SQLFiddle Demo

answered Jul 26, 2013 at 19:57

John Woo

265k70 gold badges509 silver badges500 bronze badges

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

Comments

Barmar · Accepted Answer · 2013-07-26 20:44:37Z

2

You can join the original table with a subquery that uses GROUP BY:

SELECT t1.city, citycount
FROM myTable t1
JOIN (SELECT city, COUNT(city) citycount
      FROM myTable
      GROUP BY city) t2
USING (city)

SQLFIDDLE

edited Jul 26, 2013 at 20:44

answered Jul 26, 2013 at 20:08

Barmar

789k57 gold badges555 silver badges669 bronze badges

2 Comments

Barmar Over a year ago

@fthiella Thanks. Maybe because I didn't add a sqlfiddle like 491243 did. Fixed that.

Barmar Over a year ago

@491243 Maybe because we forgot to tell the OP not to use the mysql_ extensions, even though there's no PHP in the question? :)

Collectives™ on Stack Overflow

MYSQL COUNT return duplicate rows

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related