MySQL GROUP BY returns only first row

Question

I have a table named forms with the following structure-

GROUP       | FORM       | FILEPATH
====================================
SomeGroup   | SomeForm1  | SomePath1
SomeGroup   | SomeForm2  | SomePath2
------------------------------------

I use the following query-

SELECT * FROM forms GROUP BY 'GROUP'

It returns only the first row-

GROUP       | FORM       | FILEPATH
====================================
SomeGroup   | SomeForm1  | SomePath1
------------------------------------

Shouldn't it return both (or all of it)? Or am I (possibly) wrong?

Burgle

With group, it returns only one record for grouping condition. If you have two fields Group and Form in group by condition then both record will come in result. – Sepaloid

The above behavior is correct for GROUP BY. – Son

Answer 1

Thank you everyone for pointing out the obvious mistake I was too blind to see. I finally replaced GROUP BY with ORDER BY and included a WHERE clause to get my desired result. That is what I was intending to use all along. Silly me.

My final query becomes this-

SELECT * FROM forms WHERE GROUP='SomeGroup' ORDER BY 'GROUP'

Burgle

Yes, I had a similar problem and used only 'order by' instead of group by and it worked. – Brahms

Answer 2

As the manual states:

In standard SQL, a query that includes a GROUP BY clause cannot refer to nonaggregated columns in the select list that are not named in the GROUP BY clause. For example, this query is illegal in standard SQL because the name column in the select list does not appear in the GROUP BY:

SELECT o.custid, c.name, MAX(o.payment)
  FROM orders AS o, customers AS c
  WHERE o.custid = c.custid
  GROUP BY o.custid;

For the query to be legal, the name column must be omitted from the select list or named in the GROUP BY clause.

MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the preceding query is legal in MySQL. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate.

In your case, MySQL is correctly performing the grouping operation, but (since you select all columns including those by which you are not grouping the query) gives you an indeterminate one record from each group.

Birdiebirdlike

Answer 3

It only returns one row, because the values of your GROUP column are the same ... that's basically how GROUP BY works.

Btw, when using GROUP BY it's good form to use aggregate functions for the other columns, such as COUNT(), MIN(), MAX(). In MySQL it usually returns the first row of each group if you just specify the column names; other databases will not like that though.

Demetri

Answer 4

as far as mysql is concerned, I just solved my problem by hit & trial.

I had the same problem 10 minutes ago. I was using mysql statement something like this:

SELECT * FROM forms GROUP BY 'ID'; // returns only one row

However using the statement like the following would yeild same result:

SELECT ID FROM forms GROUP BY 'ID'; // returns only one row

The following was my solution:

SELECT ID FROM forms GROUP BY ID; // returns more than one row (with one column of field "ID") grouped by ID

or

SELECT * FROM forms GROUP BY ID; // returns more than one row (with columns of all fields) grouped by ID

or

SELECT * FROM forms GROUP BY `ID`; // returns more than one row (with columns of all fields) grouped by ID

Lesson: Donot use semicolon, i believe it does a stringtype search with colons. Remove colons from column name and it will group by its value. However you can use backtick escapes eg. ID

Yelenayelich

Do you mean "single quotes" instead of "semicolon"? 'ID' is a string (i.e., text), not a column name. – Vacationist

Answer 5

Your code:

SELECT * FROM forms GROUP BY 'GROUP'

isn't very "good" SQL, MySQL lets you get away with it and returns only one value for all columns not mentioned in the group by clause. Almost any other database would not perform this query. As a rule, any column, that is not part of the grouping condition must be used with an aggregate function.

Firkin

Answer 6

Query result is perfect; it will return only one row.

Lanner

Answer 7

SELECT * FROM forms GROUP BY `GROUP` 

it's strange that your query does work

Lyman

This, in my opinion is the actual correct answer. Maybe you should elaborate more – Runstadler

Answer 8

The above result is kind of correct, but not quite.

All columns you select, which are not part of the GROUP BY statement have to be aggregated by some function (list of aggregation function from the MySQL docu). Most often they are used together with numeric columns.

Besides this, your query will return one output row for every (combination of) attributes in the columns referenced in the GROUP BY statement. In your case there is just one distinct value in the GROUP column, namely "SomeGroup", so the output will only contain one row for this value.

Swarts

Answer 9

Group by clause should only be required if you have any group functions, say max, min, avg, sum, etc, applied in query expressions. Your query does not show any such functions. Meaning you actually not required a Group by clause. And if you still use such clause, you will receive only the first record from a grouped results.

Hence output on your query is perfect.

Chekiang

Answer 10

It may be important to point out, that string characters, such as 'string' may not denote the desired column.

I tried this on MariaDb 8.0.29.

The result from query ending with

group by('dname')

gave different result than for

group by(`dname`)

group by(dname)

More importantly, in my case for my query, in the second variant I got the expected result.

It seems to me, that the query ending with group group by('dname') does not define that it shall be ordered by column of name dname, but by some string, which may be interpreted as a number, perhaps resulting in some query such as group by(15). Nevertheless, I conclude, that without the usage of backsticks, or just a plane column name group by(dname), the behavior is not defined.

Clarino

Answer 11

I recently encountered this issue as part of a PROCEDURE where the column name in the GROUP BY clause was also the name of a variable DECLAREd earlier in the procedure and which had been used in building up the temporary table used in the FROM clause. The solution was to change the name of the variable to something different from the column name.

Culver