Group functions fine by themselves, but not when added together?

Question

I have a mysql query that looks something like this:

SELECT
   SUM(reg_yr) AS reg_yr_total,
   SUM(spot_as_reg_yr) AS spot_as_reg_yr_total
FROM foo
WHERE bar
GROUP BY baz
ORDER BY reg_yr_total

which works just fine. if I want to change the ORDER BY clause to be reg_yr_total+spot_as_reg_yr_total however, I get an error stating Reference 'reg_yr_total' not supported (reference to group function).

Why can I use each of these columns by themselves, but as soon as I try to add the two together it fails? Is there a way around this?

Answer 1

If you don't want to SELECT another column, try the following:

SELECT
   SUM(reg_yr) AS reg_yr_total,
   SUM(spot_as_reg_yr) AS spot_as_reg_yr_total
FROM foo
WHERE bar
GROUP BY baz
ORDER BY SUM(reg_yr) + SUM(spot_as_reg_yr)

Answer 2

Try summing them to another virtual column:

SELECT
   SUM(reg_yr) AS reg_yr_total,
   SUM(spot_as_reg_yr) AS spot_as_reg_yr_total,
   (reg_yr_total + spot_as_reg_yr_total) AS reg_yr_total
FROM foo
WHERE bar
GROUP BY baz
ORDER BY reg_yr_total

This is untested, but should work.

---

If this is an incorrect answer, please tell me so and I will gladly remove it.