SQL weighted average

Question

Have a table like below.

make   | model | engine | cars_checked | avg_mileage
---------------------------------------|--------
suzuki | sx4   | petrol | 11           | 12
suzuki | sx4   | diesel | 150          | 16
suzuki | swift | petrol | 140          | 15
suzuki | swift | diesel | 18           | 19
toyota | prius | petrol | 16           | 17
toyota | prius | hybrid | 250          | 24

The output desired is

1. average mileage by engine (petrol, diesel)
2. average mileage by make
3. average mileage by model

Cannot do a simple group by as the weight-age in terms of the number of samples for each record (cars_checked) need to considered so as to avoid the average of averages problem.

What is the right way to achieve it ? is there a way to consider the number of samples to do a weighted average in group by ?

update - output format added for #1 above as example

engine   | mileage_by_engine
--------------------------
petrol   | xx.z
diesel   | yy.z

Answer 1

SELECT engine, SUM(cars_checked * avg_mileage) / SUM(cars_checked) AS avgMilageByEngine
FROM [YOUR_TABLE]
GROUP BY engine

SELECT make, SUM(cars_checked * avg_mileage) / SUM(cars_checked) AS avgMilageByMake
FROM [YOUR_TABLE]
GROUP BY make

SELECT model, SUM(cars_checked * avg_mileage) / SUM(cars_checked) AS avgMilageByModel
FROM [YOUR_TABLE]
GROUP BY model