CODEWITHSUNDEEP
mysqlperformancecountgroup-byquery-optimization
workwise
workwise

Reputation: 1103

Mysql: count, group by yet return all results

I have a table with fields:

id | color | other fields....

1    red
2    blue
3    green
4    green
5    red
6    blue

color is enum(red, blue, green).

What I want is list of results sorted by color, and also want to know the COUNT of elements with red/blue/green colors.

So from above table, I want the information like:

1 red 5 red 2 blue 6 blue 3 green 4 green

and want the counts as RED=2, BLUE=2, GREEN=2

If I use GROUP BY color, I get only 3 rows.

I could of course count in a separate loop outside or use 2 different queries. But any way to optimally do this in single query would be appreciated.

Upvotes: 0

Views: 204

Answers (1)

Peter Kiss
Peter Kiss

Reputation: 9319

SELECT  c.*, ColorCounts.ColorCount
FROM    colors AS c
INNER JOIN  (
            SELECT color, COUNT(*) AS ColorCount
            FROM colors
            GROUP BY color
            ) AS ColorCounts
        ON  ColorCounts.color = c.color
ORDER BY c.color, c.id

Upvotes: 1

Related Questions