Troubleshooting of MySQL errors: Difference between revisions

Troubleshooting of MySQL errors (edit)

1,303 bytes added , 18 December 2024

14,953

edits

@@ Line 278: / Line 278: @@
 ) final
 WHERE final.rank <= 10
+</pre>
+=== Error 1055: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ===
+Message
+<pre>
+Query Error (1055): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.posts.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
+</pre>
+Query with Error
+<pre>
+-- Create sample table
+CREATE TABLE posts (
+   id INT,
+   post_id VARCHAR(10),
+   author VARCHAR(50),
+   content TEXT,
+   likes INT,
+   post_time DATETIME
+);
+-- Insert sample data
+INSERT INTO posts VALUES
+(1, 'A123', 'Alice', 'First post', 10, '2024-01-01'),
+(2, 'A123', 'Bob', 'Great!', 5, '2024-01-02'),
+(3, 'A123', 'Bob', 'Nice!', 3, '2024-01-03');
+</pre>
+Solution: Fix the query by either (1) including all columns in GROUP BY or (2) using aggregate functions (like MAX, MIN, SUM, COUNT, GROUP_CONCAT)
+<pre>
+-- Method 1: Add all columns to GROUP BY
+SELECT
+   id,
+   post_id,
+   author,
+   content,
+   likes
+FROM posts
+GROUP BY post_id, author, id, content, likes;
+-- Method 2: Use aggregate functions for non-GROUP BY columns
+SELECT
+   MAX(id) as id,
+   post_id,
+   author,
+   GROUP_CONCAT(content) as contents,
+   SUM(likes) as total_likes
+FROM posts
+GROUP BY post_id, author;
 </pre>