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 ANDs 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.
