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? This is what I have:

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

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