---
title: "Mean vs median vs weighted average | Oplema"
description: "Learn when the median beats the mean, how a weighted average works, how nulls change the answer and why every average depends on the rows it covers."
url: "https://oplema.com/learn/business-intelligence/aggregation"
updated: "2026-11-04"
---

Lesson 6 of 15 · Lab

# Mean, median and weighted average in reporting

An aggregate such as an average means something only once you say which rows it covers and what it divides by. The mean is the total over the count, the median is the middle value, and a weighted average gives larger groups more say. Use the median for "typical" when a few large values pull the mean.

Five invoices of 100, 100, 100, 100 and 1100
| Measure | Result | Reads as |
| --- | --- | --- |
| Mean | 300 | The average invoice, higher than four of the five |
| Median | 100 | The typical invoice |

At [fictional Vandelay Coffee Supply](https://oplema.com/learn/business-intelligence/what-is-bi), Don Tremendous says customer Initech's invoices are unusually large. To test that, you need to know what a typical invoice looks like. This flow holds Q3's 13 invoices, with `net_eur` as net value in euros.

12 of the 13 invoices have a price, and INV-1014's is unknown. Should the average divide by 12 or by 13?

By 12. Dividing by 13 treats the unknown price as zero, and the metric says unknown prices stay empty.

## Build the mean, the median and a group

1. In this live flow in [Strata](https://oplema.com/strata), Oplema's browser app for data prep, **Summarize** is selected: `net`, `invoices` and `priced`.
2. Under **Actions**, select **Add**. **Summarize** stays red until the action has a column.
3. Set the new **Action** to **Average**, and its **Column** to `net_eur`.
4. Read the preview. Compare your new column with `net` divided by `priced`. Then select **Add**, and set the new action to **Median** of `net_eur`.
5. Select **Add** again. The new action is a **Group by**. Set its **Column** to `customer`, and read Initech's row.

The two averages, before grouping

The mean is €956.50, which is €11,478 over 12. The median is €880, because a few large invoices pull the mean up. By customer, Initech averages €1,500 across just two invoices.

## Four averages, four questions

One table, four answers
| Figure | Divides by | Answers |
| --- | --- | --- |
| €956.50 mean | 12 priced invoices | What is the average priced invoice? |
| €880 median | Middle of those 12 | What does a typical invoice look like? |
| €882.92 | 13, with the unknown as zero | Nothing true, because it invents a price |
| €1,010.50 | 4 customers' averages | What does the average customer's invoice look like? |

Initech's €1,500 is high, and two invoices is too few to call a pattern. Report the count beside the average, as `priced` does here, and that limit stays visible.

## Averaging averages, and the weighted fix

The €1,010.50 counts Initech's two invoices as heavily as Wonka's four priced ones, which changes the question without saying so. A weighted average multiplies each customer's average by its count before dividing, and that returns €956.50. Take stores with 3 sales averaging 10 and 1 sale of 50. The plain mean of the two averages is 30, and the weighted average is 20.

Use a weighted average for rates and prices across groups of different sizes, and use the plain one only when each group should count equally.

## Averages in SQL and Excel

```
SELECT  customer,
  AVG (net_eur)  AS  mean_value,
  COUNT (net_eur)  AS  priced,
  COUNT ( * )  AS  invoices
FROM  invoices
GROUP BY  customer
```

`AVG` and `COUNT(column)` skip nulls, while `COUNT(*)` counts every row. DuckDB has `MEDIAN(net_eur)`, and PostgreSQL uses `PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY net_eur)`. In Excel, use `AVERAGE`, `MEDIAN`, and `=SUMPRODUCT(averages, counts) / SUM(counts)` for a weighted average. See the [Summarize reference](https://oplema.com/docs/strata/transform/summarize) for this block.
