Mysql Error 1140
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies mysql disable only_full_group_by of this site About Us Learn more about Stack Overflow the company mysql count Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges mysql inner join Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute:
Group By
Sign up MySQL #1140 - Mixing of GROUP columns up vote 7 down vote favorite 1 Hi wondering if perhaps someone could shed some light on the below error. The sql works fine locally but i get the the below error remotely. SQL query: SELECT COUNT(node.nid), node.nid AS nid, node_data_field_update_date.field_update_date_value AS node_data_field_update_date_field_update_date_value FROM node node LEFT JOIN content_type_update node_data_field_update_date ON node.vid = node_data_field_update_date.vid WHERE node.type IN ('update') ORDER BY node_data_field_update_date_field_update_date_value DESC MySQL said: #1140 - Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause` sql mysql group-by mysql-error-1140 share|improve this question edited Oct 6 '10 at 21:11 OMG Ponies 199k37360417 asked Aug 7 '09 at 11:07 frosty 2,4301264111 What do you expect "COUNT(node.nid)" to return when you're not doing a GROUP BY? –Joachim Sauer Aug 7 '09 at 11:17 add a comment| 4 Answers 4 active oldest votes up vote 8 down vote accepted The reason a single column using an aggregate function works while the version with columns not using aggregate functions doesn't is because you need to specify a GROUP BY clause. Here's what your query should look resemble: SELECT COUNT(n.nid), n.nid, ctu.field_update_date_value FROM NODE n LEFT JOIN CONTENT_TYPE_UPDATE ctu ON ctu.vid = n.vid WHERE n.type IN ('update') GROUP BY n.nid, ctu.field_update_date_value ORDER BY field_update_date_value DESC I changed out your table aliases for shorter ones - easier to read. Here's the meat of your issue: SELECT n.nid, COUNT(n.fake_example_column), ctu.field_update_date_value ... GROUP BY n.nid,
Connectors More MySQL.com Downloads Developer Zone Section Menu: Documentation Home MySQL 5.7 Reference Manual Preface and Legal Notices General Information Installing and Upgrading MySQL Using MySQL as a Document Store Tutorial MySQL Programs MySQL Server Administration Security Backup and Recovery Optimization Language Structure Globalization Data Types Functions and Operators Function and Operator Reference Type Conversion in Expression Evaluation Operators Operator Precedence Comparison Functions and Operators Logical Operators Assignment Operators Control http://stackoverflow.com/questions/1244169/mysql-1140-mixing-of-group-columns Flow Functions String Functions String Comparison Functions Regular Expressions Numeric Functions and Operators Arithmetic Operators Mathematical Functions Date and Time Functions What Calendar Is Used By MySQL? Full-Text Search Functions Natural Language Full-Text Searches Boolean Full-Text Searches Full-Text Searches with Query Expansion Full-Text Stopwords Full-Text Restrictions Fine-Tuning MySQL Full-Text Search Adding https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html a Collation for Full-Text Indexing ngram Full-Text Parser MeCab Full-Text Parser Plugin Cast Functions and Operators XML Functions Bit Functions and Operators Encryption and Compression Functions Information Functions Spatial Analysis Functions Spatial Function Reference Argument Handling by Spatial Functions Functions That Create Geometry Values from WKT Values Functions That Create Geometry Values from WKB Values MySQL-Specific Functions That Create Geometry Values Geometry Format Conversion Functions Geometry Property Functions General Geometry Property Functions Point Property Functions LineString and MultiLineString Property Functions Polygon and MultiPolygon Property Functions GeometryCollection Property Functions Spatial Operator Functions Functions That Test Spatial Relations Between Geometry Objects Spatial Relation Functions That Use Object Shapes Spatial Relation Functions That Use Minimum Bounding Rectangles (MBRs) MySQL-Specific Spatial Relation Functions That Use Minimum Bounding Rectangles (MBRs) Spatial Geohash Functions Spatial GeoJSON Functions Spatial Convenience Functions JSON Functions JSON Function Reference Functions That Create JSON Values Functions That Search JSON Values Funct
Impact on me: None Category:MySQL Server: General Severity:S2 (Serious) Version:5.1.30-community-log OS:Microsoft Windows (2003) Assigned to: Bernt Marius Johnsen Tags: regression Triage: Triaged: D3 (Medium) View Add Comment Files Developer Edit Submission View Progress Log Contributions https://bugs.mysql.com/bug.php?id=42567 [3 Feb 2009 12:39] Boyd Pappot Description: When using "ONLY_FULL_GROUP_BY", the following query generates an https://forums.digitalpoint.com/threads/mysql-error-error-number-1140.2055781/ error: select count(*) from information_schema.statistics where information_schema.statistics.table_schema = database(); This is triggered using a where statement, without the where clause the query works as expected. The error thrown is not even correct: Mixing of GROUP columns .. with no GROUP columns while there aren't any "no GROUP" columns selected How to repeat: Server version: 5.1.30-community-log MySQL Community Server mysql error (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> use test; Database changed mysql> create table t1(number int); Query OK, 0 rows affected (0.08 sec) mysql> insert into t1 values (1), (10); Query OK, 2 rows affected (0.03 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select count(1) from t1; +----------+ | count(1) | +----------+ | 2 | +----------+ 1 row in set (0.00 sec) mysql> select mysql error 1140 count(1) from t1 where number > 5; ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause [3 Feb 2009 13:16] Valerii Kravchuk Verified just as described: C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3310 test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 5.1.30-community-log MySQL Community Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> set sql_mode=''; Query OK, 0 rows affected (0.00 sec) mysql> drop table t1; Query OK, 0 rows affected (0.09 sec) mysql> create table t1(number int); Query OK, 0 rows affected (0.11 sec) mysql> insert into t1 values(1), (10); Query OK, 2 rows affected (0.08 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select count(*) from t1 where number > 5; +----------+ | count(*) | +----------+ | 1 | +----------+ 1 row in set (0.05 sec) mysql> set sql_mode='ONLY_FULL_GROUP_BY'; Query OK, 0 rows affected (0.00 sec) mysql> select count(*) from t1 where number > 5; ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GR OUP columns is illegal if there is no GROUP BY clause [3 Feb 2009 13:19] Valerii Kravchuk 5.0.74 works OK, so this is regression: C:\Program Files\MySQL\MySQL Server 5.0\bin
You can target relevant areas of the site and show ads based on geographical location of the user if you wish. Starts at just $1 per CPM or $0.10 per CPC. mysql error - error number 1140 Discussion in 'PHP' started by antpeks, Jan 8, 2011. 0 antpeks Active Member Messages: 392 Likes Received: 4 Best Answers: 0 Trophy Points: 80 #1 i get the following error, it worked perfectly, until i moved servers and restored the backups. Query Executed: SELECT SUM(a.impression_rate) as sum_imp_rate, COUNT(a.user_id) as count_imp, b.user_id, b.username, b.email_address FROM `mmh_file_impression` AS a INNER JOIN `mmh_user_info` AS b ON a.user_id = b.user_id WHERE a.user_id = '103' AND `status` = '1'; Code (markup): Time Encountered: January 8, 2011, 11:47:21 AM URL Location: http://xxxx.xxx/admin.php?act=account-s-s&id=103 IP Address: 83.1xx.114.xxx Error: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause Error Number: 1140 Code (markup): can someone help me figuring out what is happening here.. antpeks, Jan 8, 2011 IP Minimal Hank Peon Messages: 136 Likes Received: 2 Best Answers: 0 Trophy Points: 0 #2 Turn off ONLY_FULL_GROUP_BY and the error should disappear. Minimal Hank, Jan 8, 2011 IP antpeks Active Member Messages: 392 Likes Received: 4 Best Answers: 0 Trophy Points: 80 #3 any change on a little explaination how to do this, as i am newbie in Sql coding antpeks, Jan 8, 2011 IP Minimal Hank Peon Messages: 136 Likes Received: 2 Best Answers: 0 Trophy Points: 0 #4 antpeks said: ↑ any change on a little explaination how to do this, as i am newbie in Sql codingClick to expand... One way is to switch to ANSI or to remove it from the mode list. SELECT @@sql_mode; Code (markup): Can you please show us the output ? Minimal Hank, Jan 8, 2011 IP (You must log in or sign up to reply here.) Show Ignored Content Log in with Facebook Your name or email address: Do you already have an account? No, create an account now. Yes, my password is: Forgot your password? Stay logged in Digital Point Home Forums > Development > Programming > PHP > Log in to view your Analytics, AdSense & PayPal stats Home Forums Search Forums Recent Posts Spy Search EnginesGoogleAdSenseGuidelines / CompliancePlacement / Reviews / ExamplesReporting & StatsPaymentsAdWordsAnalyticsGoogle+SitemapsGoogle APIShoppingYahooPublis