Question 394416 in Mysql Question posted in
The official MySQL documentation can be found here.

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

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?

Tags:

2

Answers


  1. 0

    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;
    Login or Signup to reply.
  2. 0

    Is this what you wanted?

    Try this,

    Select * into #Temprecords from 
(Select '2020-07-01' Record_date, 'max' Data_Type, 92 Data_Value Union 
Select '2020-07-01' Record_date, 'min' Data_Type, 71 Data_Value Union 
Select '2020-07-01' Record_date, 'avg' Data_Type, 74 Data_Value Union 
Select '2020-07-02' Record_date, 'max' Data_Type, 90 Data_Value Union 
Select '2020-07-02' Record_date, 'min' Data_Type, 67 Data_Value Union 
Select '2020-07-02' Record_date, 'avg' Data_Type, 77 Data_Value ) A


;With Temprecords as   
(Select Month(Record_date) Mth
  ,Case Data_Type When 'min' Then min(Data_Value) Else 0 END as MinTemp
  ,Case Data_Type When 'max' Then max(Data_Value) Else 0 End as MaxTemp
  ,Case Data_Type When 'avg' Then avg(Data_Value) Else 0 End as AvgTemp
from #TempRecords
Group By Month(Record_date), Data_Type
)

Select Mth as 'Month', SUM(MinTemp) 'Min', SUM(MaxTemp) 'Max', SUM(AvgTemp) 'AVG'
From Temprecords 
Group By Mth
  
DROP table #TempRecords
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search