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
Advertisement
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