Marco Marco - 3 months ago 8
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?
Thanks

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