How to get MySQL count within grouping

Question

Given this table.

+----+-------+-------+
| id | color | grain | 
+----+-------+-------+
|  1 |   A   |   B   |
|  1 |   A   |   B   |
|  1 |   A   |   B   |
|  2 |   B   |   X   |
|  2 |   B   |   X   |
|  2 |   B   |   Z   |
|  2 |   E   |   E   |
|  3 |   A   |   C   |
|  3 |   A   |   B   |
|  3 |   A   |   B   |
+----+-------+-------+

What would be the MySQL query to produce the following result. I need to count the number of unique occurrences of color/grain combination within each id.

+----+-------+-------+-------------------+
| id | color | grain | color/grain count |
+----+-------+-------+-------------------+
|  1 |   A   |   B   |         1         |
|  2 |   B   |   X   |         3         |
|  2 |   B   |   Z   |         3         |
|  2 |   E   |   E   |         3         |
|  3 |   A   |   C   |         2         |
|  3 |   A   |   B   |         2         |
+----+-------+-------+-------------------+

This is my current query, but it does not produce the count I am looking for. The count is for the occurrences of the group by clause, not the unique occurrences within id.

select id,color,grain,count(id)
  from table
group by id,color,grain
order by id;

count the occurrences of the group of color and grain within id. In other words, id 2 has 3 unique combinations of color and grain, while id 3 has 2 unique combinations and id 1 has on 1 unique combination of color and grain. — panofish, Mar 18 '15 at 01:49

score 1 · Accepted Answer · answered Mar 18 '15 at 00:01

select  id,
        color,
        grain,
        count(*) 
    from table
    group by id, color, grain

produces this:

id  color   grain   count
1   A       B       3
2   B       X       2
2   B       Z       1
2   E       E       1
3   A       B       2
3   A       C       1

Which is almost there. Now you need to change that to show the number of distinct records by id, so we can do that by using a sub-query:

select  x.id,
        COUNT(*)
    from (
        select  id,
                color,
                grain,
                COUNT(*) cnt
            from #table
            group by id, color, grain
        ) x
    group by x.id

which results in:

id  count
1   1
2   3
3   2

Now, to get the expanded results, join:

select a.id, a.color, a.grain, b.cnt
    from (
        select  id,
                color,
                grain,
                COUNT(*) cnt
            from #table
            group by id, color, grain
        ) a
    join (
        select  x.id,
                COUNT(*) cnt
            from (
                select  id,
                        color,
                        grain,
                        COUNT(*) cnt
                    from #table
                    group by id, color, grain
                ) x
            group by x.id
        ) b on
        a.id = b.id

which results in:

id  color   grain   cnt
1   A       B       1
2   B       X       3
2   B       Z       3
2   E       E       3
3   A       B       2
3   A       C       2

I'm sure this will work, but I was hoping for something simpler. I will test tomorrow. — panofish, Mar 18 '15 at 05:51
I ultimately only needed a variation of the second select statement you provided. Thanks. — panofish, Mar 18 '15 at 15:29

How to get MySQL count within grouping

1 Answers1