ERROR 1055 (42000): 'mediabankdb_0_9_7.c.code' isn't in GROUP BY
Posted by:
h h
Date: May 29, 2013 08:07PM
i have a problem
mysql> select substring_index(c.code,'-',1),sum(s.exec_amount),sum(s.exec_count) from stat_exec_buy s,comm_category c where s.category_id = c.id and substring_index(c.code,'-',1
) in ('|CN1', '|CN2', '|CN3', '|CN4', '|CN5', '|CN6', '|CN7', '|CN8', '|CN9', '|CN10', '|CN11', '|CN12', '|CN13', '|CN14', '|CN15', '|CN16', '|CN17', '|CN18', '|CN19', '|CN20',
'|CN20', '|CN21', '|CN22') and s.stat_date >= '2013-05-23 00:00:00' and s.stat_date <= '2013-05-29 00:00:00' group by substring_index(c.code,'-',1);
ERROR 1055 (42000): 'mediabankdb_0_9_7.c.code' isn't in GROUP BY
mysql> select @@sql_mode;
+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode |
+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> set @@sql_mode='PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)
mysql> select @@sql_mode;
+--------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode |
+--------------------------------------------------------------------------------------------------------------------------------------+
| PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select substring_index(c.code,'-',1),sum(s.exec_amount),sum(s.exec_count) from stat_exec_buy s,comm_category c where s.category_id = c.id and substring_index(c.code,'-',1
) in ('|CN1', '|CN2', '|CN3', '|CN4', '|CN5', '|CN6', '|CN7', '|CN8', '|CN9', '|CN10', '|CN11', '|CN12', '|CN13', '|CN14', '|CN15', '|CN16', '|CN17', '|CN18', '|CN19', '|CN20',
'|CN20', '|CN21', '|CN22') and s.stat_date >= '2013-05-23 00:00:00' and s.stat_date <= '2013-05-29 00:00:00' group by substring_index(c.code,'-',1);
+-------------------------------+--------------------+-------------------+
| substring_index(c.code,'-',1) | sum(s.exec_amount) | sum(s.exec_count) |
+-------------------------------+--------------------+-------------------+
| |CN1 | 97969.3200 | 55015 |
| |CN10 | 108459.7000 | 59689 |
| |CN11 | 117477.1100 | 65884 |
| |CN12 | 117570.5900 | 65840 |
| |CN13 | 118662.6300 | 67330 |
| |CN14 | 118580.4300 | 67579 |
| |CN15 | 109519.1900 | 61359 |
| |CN16 | 108372.5400 | 61808 |
| |CN17 | 103793.6000 | 58221 |
| |CN18 | 111085.9500 | 62761 |
| |CN19 | 109624.2900 | 62256 |
| |CN2 | 2425.3770 | 16261 |
| |CN20 | 108436.1300 | 62922 |
| |CN3 | 2579.8760 | 16990 |
| |CN4 | 116622.7700 | 64444 |
| |CN5 | 2596.2750 | 16478 |
| |CN6 | 117531.1000 | 67002 |
| |CN7 | 119634.9800 | 66749 |
| |CN8 | 108170.1300 | 61540 |
| |CN9 | 108606.6900 | 61341 |
+-------------------------------+--------------------+-------------------+
buy when i use workbench to run the query ,it's ok and sql_mode has ONLY_FULL_GROUP_BY option