I'm working on a small project for my portfolio and relatively new to SQL, came across an unguided project with this question.

Over the course of the year in Canada, with the date of 2022-01-01 , how many cities had an average city temperature of less than 0 degrees? Please note there are discrepancies in temperature readings so please account accordingly. This is what I have:

SELECT COUNT(temp) 
FROM table
WHERE date = '2022-01-01'
HAVING AVG(temp) < 0

This returns 0 results which shouldn't be the case given the dataset. Assuming anything reading below -40 and above 40 is not usable how should I include in the same query.

edit: To clarify, I'm trying to find the count of cities where on 2022-01-01 their daily average temperature is less than 0 degrees as there are more than one temperature reading for each city each day spanned throughout the day. Hope this helps and thanks in advance.

CodePudding user response：

Deconstructing your question:

1. select only valid temps and date
2. group by city
3. select city and avg
4. consider only grouped values below 0

    select city, avg(temp)
     where temp between -40 and 40
       and date = '2022-01-01'
     group by city
    having avg(temp) < 0

CodePudding user response：

SELECT city,COUNT(temp) FROM table WHERE date = '2022-01-01' group by city HAVING AVG(temp) < 0