Getting averages based on column values Editing because I made a mistake transforming from my real example with lots of irrelevant details to the boxes example:
I have a table BOXES like this:
box_id  | color | dimension | value
1          blue     length      1       
1          blue     width       2      
2          blue     length      3      
2          blue     width       4      
3          red      length      5   
3          red      width       6      
4          red      length      7      
4          red      width       8     
5          green    length      9      
5          green    width       10 
6          green    length      11     
6          green    width       12   

And I want to output average dimension for each color, like this:
color | avg_length | avg_width 
blue        2           3        
red         6           7         
green       10          11         

Is there a SQL query to do so? I thought of doing 3 different queries like
WITH avg_len as (
  SELECT AVG(length) as l,color FROM `boxes`
), avg_width as (
  SELECT AVG(width) as w,color FROM `boxes`
), avg_height as (
  SELECT AVG(height) as h,color FROM `boxes`

but not sure how to tie them together. In my real data, there are thousands of &quot;boxes&quot; and a dozen or so &quot;colors&quot;.
SELECT color, 
       AVG(CASE WHEN dimension = 'length' THEN value END) avg_length, 
       AVG(CASE WHEN dimension = 'width' THEN value END) avg_width
FROM boxes
GROUP BY color;

fiddle