Heya, Tom.
The thing about aggregate functions such as COUNT, MAX, etc., is that they are not actually calculated until the rest of the query is evaluated. For example, when you want to select the COUNT of all the rows in a table, MySQL will first compile all the rows from the table and then find the count.
This can cause some problems. For example, what if you only want to return results where the COUNT is greater than 3? Or expressed in code:
-
SELECT
-
`name`,
-
COUNT(`id`)
-
AS `count`
-
FROM
-
`Table`
-
WHERE
-
`count` > 3
-
GROUP BY
-
`name`;
-
The problem here is that MySQL doesn't calculate the value of `count` until it compiles the entire result set. In other words, MySQL won't calculate `count` until it finds all rows where `count` is greater than three. Oops.
The solution to the above problem is to use the HAVING keyword, which is a special WHERE that gets applied after the initial result set gets calculated:
-
SELECT
-
`name`,
-
COUNT(`id`)
-
AS `count`
-
FROM
-
`Table`
-
GROUP BY
-
`name`
-
HAVING
-
`count` > 3;
-
Or at least that's what "Invalid use of group function" means.
Of course, the problem that you were hitting was a little different. Simply put, you have to use a column name, instead of a calculated value, in the ORDER BY clause.
For example, in your query:
-
SELECT
-
`name`,
-
SUM(`total`)
-
AS `numtotal`
-
FROM
-
`totals`
-
GROUP BY
-
`name`
-
ORDER BY
-
SUM(`total`) DESC
-
You are trying to ORDER BY the value of the SUM of the `total` column (for example, ORDER BY 60 DESC). Which strikes MySQL as kind of silly, so it throws a silly error at you.
Now you might say, 'Hey, wait a second! I got it working before!'.
For example this works:
-
SELECT
-
`name`,
-
SUM(`total`)
-
FROM
-
`totals`
-
GROUP BY
-
`name`
-
ORDER BY
-
SUM(`total`) DESC
-
because MySQL is clever enough to see that you are SELECTing the SUM() as one of the columns, so it uses that column to order the results. However, once you rename the SUM() column, MySQL no longer recognizes it unless you use the alias that you assigned it.
At any rate, glad to hear you got it working! Good luck with your project, and if you ever need anything, post back anytime :)