Perform different aggregation functions on different rows of the output returned by group by clause

Question

I came across this question on a coding platform One single table is given. It is of the following format:

Temperature_records

Record_date	Data_type	Data_value
2020-07-01	max	92
2020-07-01	min	71
2020-07-01	avg	74
2020-07-02	max	90
2020-07-02	min	67
2020-07-02	avg	77

The table consists of the minimum, maximum and average temperature readings for each day for six months in 2020. We need to calculate the monthly minimum, monthly average, monthly maximum temperatures. The output should be like this: (The min, max and avg temperatures should be rounded off to the nearest integer)

Month	Min	Max	Avg
7	92	99	95
8	91	100	94

Now, I have used the following query:

select table1.month, table1.data_type, table1.data_value
from (
    select substring(record_date, 6, 2) as "month", data_type, data_value
    from temperature_records
) table1
order by table1.month, table1.data_type;

This query gives me an output that looks like this:

Month	data_type	data_value
7	avg	97
7	avg	98

It returns the month-wise records Now if I group the records by (month, data_type), then I need to perform an aggregation function on the records depending on their data_type. How can I do that? Also, how can I return the records according the desired output format?

Answer 1

We can use conditional aggregation here for a general solution:

SELECT
    MONTH(Record_date) AS Month,
    MAX(CASE WHEN Data_type = 'min' THEN Data_value END) AS Min,
    MAX(CASE WHEN Data_type = 'max' THEN Data_value END) AS Max,
    MAX(CASE WHEN Data_type = 'avg' THEN Data_value END) AS Avg
FROM temperature_records
GROUP BY 1
ORDER BY 1;