I want to create a view, named route that selects the road's name and length, which have a grea-CodePudding

CREATE VIEW routes AS SELECT name, length FROM roads WHERE length >= AVG(length) GROUP BY name;

CodePudding user response：

I suspect your question is about how to use average in this case. You could write as a subquery, but may depend on your version of MySql.

CREATE VIEW longroads AS
   SELECT 
     name, length
   FROM  
     roads
   WHERE 
     length >= (select avg(length) from roads);

Note: I'm not sure on your intention to have GROUP BY in your query, but I don't believe you want it. And I would consider changing the name of your "length" column.