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?
2
Answers
We can use conditional aggregation here for a general solution:
Is this what you wanted?
Try this,