SQL Group By min(len())-CodePudding

select min(length(city)), city
from station
group by min(length(city));

select max(length(city)), city
from station
group by min(length(city));


output = ERROR 1056 (42000) at line 1: Can't group on 'min(length(city))'

I am trying to get minimum and maximum char length with city name. I thought this algorithm will be okay.

Why ı can't group on min(length(city))

CodePudding user response：

It doesn't make sense to group by an aggregate function. Aggregate functions are called after grouping, to calculate something for all the rows in the group (such as a sum total).

You can use a subquery to calculate an aggregate for the entire table. Then you can compare this with individual rows.

SELECT city, LENGTH(city) AS length
FROM station
WHERE LENGTH(city) = (SELECT MIN(length(city)) FROM station);

CodePudding user response：

Another solution :

 SELECT CITY , LENGTH(city) AS length
    FROM STATION
    WHERE LENGTH(CITY)  
          IN (
          SELECT  MAX(LENGTH(CITY)) 
          FROM STATION
          UNION 
          SELECT  MIN(LENGTH(CITY)) 
          FROM STATION )

CodePudding user response：

select city, length(city) from station
order by length(city),city asc
limit 1;

select city, length(city) from station
order by length(city) desc
limit 1;