'SQL Query to find maximum, average, minimum of temperature

I was attending a question in hackerank advanced SQL certification, i came across a question like to find the maximum, minimum and average of temperature . i wrote query evrrything was perfect except Average

Below is my query

SELECT MONTH(record_date) , MAX(data_value) AS max,min(data_value) as min,
Round(sum(data_value)/count(data_value))  as avg

FROM temperature_records
Where Month(record_date)<=12 and Month(record_date)>=7
GROUP BY MONTH(record_date),data_type   order by MONTH(record_date);

Can anyone guide me on what was mistake in average .

sql


Solution 1:[1]

Just use the average function AVG() here:

SELECT MONTH(record_date), MAX(data_value) AS max, MIN(data_value) AS min,
       AVG(data_value) AS avg
FROM temperature_records
WHERE MONTH(record_date) BETWEEN 7 AND 12
GROUP BY MONTH(record_date)
ORDER BY MONTH(record_date);

Solution 2:[2]

-- Because there is a relation between data_type column and data_value column we may use the following syntax in Postgres:

    SELECT  EXTRACT(MONTH from (record_date)) AS month,
            max(data_value) filter (where data_type like 'max') AS Maximum,
            min(data_value) filter (where data_type like 'min') AS Minimum,
            cast(AVG(data_value) filter (where data_type like 'avg') as INTEGER) AS Average
    FROM    temperature_records
    WHERE EXTRACT(MONTH from (record_date)) > 6
    GROUP BY data_type, record_date
    ORDER BY month, Maximum, Minimum DESC ;

Solution 3:[3]

    SELECT MONTH(record_date), MAX(data_value) AS max, MIN(data_value) AS min,
       round(AVG(CASE WHEN data_type = 'avg' then data_value END)) AS avg
FROM temperature_records
WHERE MONTH(record_date) BETWEEN 7 AND 12
GROUP BY MONTH(record_date)
ORDER BY MONTH(record_date);

Try this

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Tim Biegeleisen
Solution 2 Ahmed Abouraia
Solution 3 user9446396

Related Questions