calculate average for group based on element value

I have a table with 2 columns: Model and Price

Model       Price
Pilot       30
Civic       20
Highlander  10
Corolla     40
Rav4        25

I know that Pilot and Civic is Honda car while Highlander, Corolla, Rav4 is Toyota. I want to calculate, say average price for Honda group (30+20)/2 and Toyota (10+40+25)/3.

Output table:

Mode    Average_price
Honda   25
Toyota  25

My problem is that I am NOT allowed to create new table or new column in existing table.

Thus I think somewhere in the query I need to list all condition say

IF Model in ('Pilot','Civic') then Manufacturer='Honda' else if Model in ('Highlander','Corolla','Rav4') then Manufacturer='Toyota'
Then average by Manufacturer.

Can anyone please help me to write a query?

Thank you,

Harry

Answer

You can use a case expression:

select 
    case 
        when model in ('Pilot', 'Civic') then 'Honda'
        when model in ('Highlander', 'Corolla', 'Rav4') then 'Toyota'
    end mode,
    avg(price) avg_price
from mytable
group by
    case 
        when model in ('Pilot', 'Civic') then 'Honda'
        when model in ('Highlander', 'Corolla', 'Rav4') then 'Toyota'
    end 

Some databases extend the SQL standard by allowing the use of aliases in the group by clause (eg MySQL), so you can simplify it as follows:

group by mode