# How is it possible that this "sub-calculation" of the "main calculation" actually gives a HIGHER value?

I do this to calculate the average food expenses per month:

``````    SELECT avg(amount)
FROM
(
SELECT date_trunc('month', "timestamp"), SUM("amount") AS amount
FROM bookkeping WHERE category = 'Food'
AND "timestamp" >= now() - INTERVAL '6 months'
GROUP BY date_trunc('month', "timestamp")
) a
``````

Then I do this, to see how much of that is from food orders online:

``````    SELECT avg(amount)
FROM
(
SELECT date_trunc('month', "timestamp"), SUM("amount") AS amount
FROM bookkeping WHERE category = 'Food'
AND "is online" = true
AND "timestamp" >= now() - INTERVAL '6 months'
GROUP BY date_trunc('month', "timestamp")
) a
``````

The second query shockingly gives a HIGHER value than the first, even though it's obviously a sub-post of the first.

How can this be possible? How can something which introduces more `AND`s still give a higher value? I've stared at this code and tried numerous things; it's not a hallucination. I must be missing something fundamental. The queries cannot be calculating what I think they do.

I've relied on the first query (and many similar to it) for a long time for my entire economy, so it really would be bad if this turns out to be fundamentally wrong...

(The reason I don't just do `SELECT avg(amount)` directly is that then this becomes the average cost per purchase instead of the average cost per month.)

In the visual table in my GUI, I have it so that it shows "Food: blablabla/month", then underneath a list of each "type" of food, so each of those "sub-groups" cannot have a number higher than the "parent".

I guess my brain fundamentally cannot understand how an average (or any number) can be higher for a sub-group of a bigger set which yields a smaller number.

Imagine you keep track of all your purchases, from chocolate bars to houses and cars, over your entire lifetime. Maybe you buy 5,000 chocolate bars at prices varying between \$1 each and \$2 each. For an average price of, for the sake of this example, \$1.47. Then you buy a car, priced at \$40,000. Now, the average price of the chocolate bars hasn't changed, but if you add in the car to that set, and recalculate the average you'll be at an average of something like \$2.26. That skew of outsized members of a set is why statisticians occasionally employ standard deviation to get rid of outliers. In this example, if you remove any purchase from the set that has a price higher than 3 standard deviations from the mean, you return the average of the set back down to \$1.47.

Your question shows that have two different sets of numbers that you're combining into a single larger set, eating in a restaurant, and getting delivery. Imagine this is the set of purchases made in a restaurant:

```╔═════╗
║ 134 ║
║ 129 ║
║ 105 ║
║ 118 ║
║  99 ║
║ 189 ║
║ 134 ║
║ 190 ║
║ 144 ║
║ 157 ║
║ 102 ║
║ 186 ║
╚═════╝```

The average for that set of purchases is \$140.58

Now, lets take the set of purchases for delivery meals:

```╔═════╗
║ 289 ║
║ 275 ║
║ 209 ║
║ 189 ║
║ 305 ║
║ 190 ║
║ 232 ║
║ 287 ║
║ 200 ║
║ 270 ║
║ 186 ║
╚═════╝```

The average for that set is \$239.27.

If, however, you combine the sets together, as in:

```╔═════╗
║ 289 ║
║ 134 ║
║ 129 ║
║ 105 ║
║ 118 ║
║  99 ║
║ 275 ║
║ 209 ║
║ 189 ║
║ 305 ║
║ 134 ║
║ 190 ║
║ 232 ║
║ 144 ║
║ 157 ║
║ 287 ║
║ 102 ║
║ 200 ║
║ 270 ║
║ 186 ║
║ 289 ║
║ 134 ║
║ 129 ║
║ 105 ║
║ 118 ║
║  99 ║
║ 275 ║
║ 209 ║
║ 189 ║
║ 305 ║
║ 134 ║
║ 190 ║
║ 232 ║
║ 144 ║
║ 157 ║
║ 287 ║
║ 102 ║
║ 200 ║
║ 270 ║
║ 186 ║
╚═════╝```

The average is \$187.70, which as you can see is less than the average for eating out, and higher than the average for eating in a restaurant.