CODEWITHSUNDEEP
sql
Harry
Harry

Reputation: 129

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

Upvotes: 0

Views: 99

Answers (2)

GMB
GMB

Reputation: 222652

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

Upvotes: 1

Mark Moretto
Mark Moretto

Reputation: 2348

How about a common table expression?

;WITH hondaCTE AS (
    SELECT 'Honda' AS [make]
    ,   SUM(ct.Price) / COUNT(1) AS [avg_price]
    FROM car_table AS ct
    WHERE ct.Model IN ('pilot','civic')
)
, toyotaCTE AS (
    SELECT 'Toyota' AS [make]
    ,   SUM(ct.Price) / COUNT(1) AS [avg_price]
    FROM car_table AS ct
    WHERE ct.Model NOT IN ('pilot','civic') -- Exclude Honda models
)
-- Union results
SELECT * FROM hondaCTE
UNION
SELECT * FROM toyotaCTE

Output:

make    avg_price
Honda   25
Toyota  25

Upvotes: 0

Related Questions