Invalid use of group function

[tom_b]

Hi,

I'm not really sure if this is a PHP or Mysql issue, but I need some help!!

I have some php scripts to use with a mysql database. I've tested everything locally on my computer and they work fine, but when I put them on-line I get the message "Invalid use of group function". Here are the group function statements:

$query = 'SELECT id, name, total FROM totals where id IN(select max(id) from totals group by name) ORDER BY total DESC ';
 
$query = 'SELECT name, SUM(total) AS numtotal FROM totals group by name order by sum(total) desc';

Any ideas why they won't work on-line will be greatly appreciated!!

Thanks, Tom

[pbmods]

Heya, Tom.

Please use CODE tags when posting source code. See the REPLY GUIDELINES on the right side of the page next time you post.

Which query is generating the error?

[tom_b]

Hi,

Sorry about the code tags. When I ran these queries through phpMyAdmin the first one worked fine, the second one generated this message;

#1111 - Invalid use of group function

Thanks, Tom

[pbmods]

Heya, Tom.

SELECT
        `name`,
        SUM(`total`)
            AS `numtotal`
    FROM
        `totals`
    GROUP BY
        `name`
    ORDER BY
        `numtotal` DESC
 

Note that since you're already naming your SUM() field, you can use that name in your ORDER BY clause.

From what I've read, MySQL generates this error when an aggregate function is used in a WHERE clause. Of course, you have no WHERE clause in your query, but I wonder if the error was caused by specifying a column alias for your aggregate function, but then not using the alias in the ORDER BY clause.

[tom_b]

Hi,

That did it!!! I THINK I understand your explanation, I guess the on-line server is a little fussier than running it locally. In any case it works perfectly, thank you very much!!

Tom

[pbmods]

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 :)

[tom_b]

Hi,

Well, I certainly don't have the Mysql expertise you have, but your explanation does make sense. These are the kinds of things I learn every time I run into a new problem!! I'll certainly remember "having" in the future. As I said, everything worked locally, I'm now in the process of getting it on-line (first time using an on-line database!!), it will be interesting to see if any of the other pages have problems. If so I'd love to be able to run them by you!! Well, thanks again, I appreciate your time and effort to educate me a little!!

Tom

[SubFire]

Thank to "pbmods" post #4

I had the same problem solve by you hint of the where clause.

that was hard to find with my 100 sql lines

[merlinrajaselvi]

clear explanation about 'having' clause, Thanks :)

@pbmods