Is it possible to use the result of SUM in a case? My current query is like this:
SELECT USERNAME, SUM(AMOUNT),
CASE SUM(AMOUNT)
WHEN SUM(AMOUNT) = 0.0 THEN 1
WHEN SUM(AMOUNT) < 50.0 THEN 2
WHEN SUM(AMOUNT) > 75.0 THEN 4
END AS gid, 0 FROM payments GROUP BY USERNAME;
That looks fine to me, but generates this:
censored 13.00 NULL 0
Is it even possible to do that?
Due to the constraints on the aggregate functions, as mentioned in Marc B's answer, an alternate solution is to fetch the records first and then apply aggregate functions on the desired fields.
SELECT
USERNAME,
SUM_AMOUNT,
CASE
WHEN SUM_AMOUNT = 0 THEN 1
WHEN SUM_AMOUNT < 50 THEN 2
WHEN SUM_AMOUNT > 75 THEN 4
END AS gid,
0
FROM (
SELECT USERNAME, SUM(AMOUNT) SUM_AMOUNT,
FROM payments
GROUP BY USERNAME
) USER_AMOUNTS
Not possible like this - Aggregate functions are calculated AFTER the whole result set is known, but case is evaluated at the per-row level.
You need to this in a HAVING clause.
