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

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 ju开发者_如何转开发st 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?

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)

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.