MySQL count rows with two duplicate column values

Question

I would like to count rows with the same serv AND spec. The below query counts the rows correctly:

SELECT *, COUNT(*) AS c
FROM table1
GROUP BY serv, spec

But I would like the output displayed as such:

Original:

+-----+------+------+
| id  | serv | spec |
| 500 | 1    | 4    |
| 501 | 5    | 1    |
| 502 | 1    | 4    |
| 503 | 1    | 5    |
| 504 | 5    | 6    |
| 505 | 2    | 4    |
| 506 | 5    | 1    |
| 507 | 2    | 4    |
| 508 | 4    | 3    |
| 509 | 2    | 4    |
+-----+------+------+

Desired output:

+-----+------+------+-------+
| id  | serv | spec | count |
| 500 | 1    | 4    | 2     |
| 502 | 1    | 4    | 2     |
| 503 | 1    | 5    | 1     |
| 505 | 2    | 4    | 3     |
| 507 | 2    | 4    | 3     |
| 509 | 2    | 4    | 3     |
| 508 | 4    | 3    | 1     |
| 501 | 5    | 1    | 2     |
| 506 | 5    | 1    | 2     |
| 504 | 5    | 6    | 1     |
+-----+------+------+-------+

fthiella · Accepted Answer · 2015-11-23 20:52:02Z

3

Since MySQL doesn't support window functions, you have do the counts in a subquery, and then join this subquery to the table:

SELECT t.id, t.serv, t.spec, c.cnt
FROM
  table1 t INNER JOIN (
    SELECT serv, spec, COUNT(*) as cnt
    FROM table1
    GROUP BY serv, spec
  ) c ON t.serv = c.serv AND t.spec=c.spec

answered Nov 23, 2015 at 20:52

fthiella

49.2k15 gold badges96 silver badges108 bronze badges

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

Collectives™ on Stack Overflow

MySQL count rows with two duplicate column values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related