Note that "group by colour" appears twice in the statement. This creates maintenance problems. If you need to change this, say to join bricks to another table, you have to do this in two places.

Using CTEs, you can do the group by once. Then refer to it in your select
#Oracle
#Reusable_Subqueries_2