Home I want to create a view, named route that selects the road's name and length, which have a greater than or equal to average length. Here is my script

Questions

I want to create a view, named route that selects the road's name and length, which have a greater than or equal to average length. Here is my script

byMR

October 4, 2022

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

>Solution :

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.