I have below table which got readings on different days in a month. Please help me to populate the name,monthly maximum,monthly minimum,monthly average from the table.

output should be

I tried

select name,month,max(data_value) as month_max,min(data_value) as month_min from table

which helping some thing. but i unable to figure how to implement logic for monthly average.

CodePudding user response：

You may use conditional aggregation as the following:

SELECT name, month, 
       MAX(CASE data_type WHEN 'max' THEN data_value END) month_max,
       MIN(CASE data_type WHEN 'min' THEN data_value END) month_min,
       AVG(CASE data_type WHEN 'avg' THEN data_value END) month_avg  
FROM table_name
GROUP BY name, month
ORDER BY name, month

See a demo.