1

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.

name month data_type data_value
raj 7 max 100
raj 7 min 20
raj 7 avg 80
raj 7 max 90
raj 7 avg 75
raj 7 min 30
raj 7 max 95
raj 7 avg 70
raj 7 avg 65
raj 7 max 85
raj 7 max 96
raj 7 avg 70
raj 7 min 35
raj 7 min 45
raj 7 avg 84
dev 8 max 125
dev 8 min 35
dev 8 avg 50
dev 8 max 120
dev 8 min 20
dev 8 avg 70
dev 8 max 100
dev 8 min 45
dev 8 avg 83
dev 8 max 70
dev 8 min 58
dev 8 avg 95
dev 8 max 110
dev 8 min 60
dev 8 avg 90
dev 8 max 115
dev 8 min 50
dev 8 avg 67
dev 8 max 97
dev 8 min 45
dev 8 avg 84

output should be

name month month_max month_min month_avg
raj 7 100 20 74
dev 8 125 20 77

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.

indianbro
  • 29
  • 4
  • 1
    What did you try so far? – Romeo Ninov Sep 14 '22 at 17:35
  • 1
    Please provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example) of your attempt to resolve this yourself, what the result of it is, and how it's different from the result you need. – Jesse Sep 14 '22 at 17:38
  • @RomeoNinov 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. – indianbro Sep 14 '22 at 17:42
  • @Jesse updated the question please check – indianbro Sep 14 '22 at 17:47
  • According to your input table, the month_max value for dev should be 125 not 120 – ahmed Sep 14 '22 at 17:56
  • Does this answer your question? [Oracle SQL pivot query](https://stackoverflow.com/questions/4841718/oracle-sql-pivot-query) – astentx Sep 14 '22 at 23:31

1 Answers1

1

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.

ahmed
  • 9,071
  • 3
  • 9
  • 22
  • 1
    wonderful, this should work out. thank you. I never knew we can use case inside of the aggregate functions – indianbro Sep 14 '22 at 17:57