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 - average mileage by engine (petrol, diesel) - average mileage by make - 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 ```

Accepted Answer

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

SQL weighted average

Solution

Problem