MySQL Bugs: #63996: column_fuction(group

Bug #63996	column_fuction(group_function)) error
Submitted:	11 Jan 2012 16:36	Modified:	12 Jan 2012 12:42
Reporter:	pepeluis76 pepeluis76	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S4 (Feature request)
Version:	5.5.19	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	Error Code: 1111. Invalid use of group function

Description:
error 1111 :  query error -> select column_function (group_function) from ....

Example:
 
select max(sum(dto)) from line_fac
group by codart

How to repeat:
*Table:
line_fact -> codline, codfact, codart, price, dto

*query ok:
select sum(dto) from line_fac
group by codart

execution sample OK:
codart
a1 -> 57
a2 -> 72
a3 -> 88

*query error 1111:
select max(sum(dto)) from line_fac
group by codart

execution sample ERROR 1111:
88  (MAXIMUN a1,a2,a3)

Suggested fix:
enable use of column_function(group_function)

This is easy to repeat:

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -uroot -proot -P3312 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 5.5.19 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table line_fact(codline int, codfact int, codart int, price int, d
to int);
Query OK, 0 rows affected (1.33 sec)

mysql> insert into line_fact(codart, dto) values (1,1), (1,2), (2,1), (2,2), (2,
3), (3,1), (3,2), (3,3), (3,4);
Query OK, 9 rows affected (0.23 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql> select codart, sum(dto) from line_fact group by codart;
+--------+----------+
| codart | sum(dto) |
+--------+----------+
|      1 |        3 |
|      2 |        6 |
|      3 |       10 |
+--------+----------+
3 rows in set (0.13 sec)

mysql> select sum(dto) from line_fact group by codart;
+----------+
| sum(dto) |
+----------+
|        3 |
|        6 |
|       10 |
+----------+
3 rows in set (0.01 sec)

mysql> select max(sum(dto)) from line_fact group by codart;
ERROR 1111 (HY000): Invalid use of group function

But according to http://dev.mysql.com/doc/refman/5.5/en/group-by-functions.html:

"This section describes group (aggregate) functions that operate on sets of values."

So, this is how you should use it:

mysql> select max(sum) from (select sum(dto) sum from line_fact group by codart)
 t;
+----------+
| max(sum) |
+----------+
|       10 |
+----------+
1 row in set (0.03 sec)

ok!!! thank you

SELECT max(suma) from 
(select sum(dto) suma from line_fac
group by codart) sumas