SQL Create a column with conditions from a sub query-CodePudding

I have a table with ID and HEIGHT, LENGTH and WIDTH. I need to find the mas measure of every row and then create a column of surcharge of $5 if the biggest measure is between 22 and 30 and 8 if it is >30. The first parte is working fine

select id, max(measure) as max_measure
from (
        select id, height as measure from table1 
        union
        select id, length as measure from table1 
        union
        select id, width as measure from table1 
     ) m
group by id

But i cant make the second part, it should be a sub query using the results I got from the first part and looking something roughly like this

select surcharge where 
        m.max_measure >= 22 and m.max_measure <30 
        m.max_measure>= 30

CodePudding user response：

select id, 
       max(measure) as max_measure,
       case when max(measure) >= 30 then 8
            when max(measure) >= 22 then 5
            else 0 end as surcharge