Marco Marco - 1 year ago 43
MySQL Question

mySQL: how many in a group

For testing purposes, here's a table (release) that looks similar to real data

release_id group_id
1 5
2 5
3 6
4 7

For each release, i need to find how many releases there are in the group. The result set should look like this

release_id total
1 2
2 2
3 1
4 1

In other words, release_id 1 belongs in group 5, and in group 5 there are 2 releases.... release_id 4 is in group 7 and there is only 1 release in group 7... and so on

I've try something like this

SELECT r.release_id, COUNT(r.group_id) total
FROM `release` r
GROUP BY (r.group_id)

But, i don't get exactly the expected result set.

Any ideas how to build the query?

Answer Source
    R.release_id, GC.cnt total
    (SELECT group_id, COUNT(*) cnt FROM release GROUP BY group_id) GC
    JOIN release R
    ON GC.group_id = R.group_id