Neoaptt Neoaptt - 6 months ago 9
MySQL Question

MYSQL using AND in group by statement instead of commas

What is the difference between using

AND


SELECT
id, text
FROM
table
GROUP BY id and text


And using comma
,


SELECT
id, text
FROM
table
GROUP BY id, text


It seams to group things together if they have either one of the elements.

IE

a,a
a,b
b,b
b,b
x,z


using
and
returns

a,a
x,z


using
,
returns

a,a
a,b
b,b
x,z


What is the purpose of this and why would one use it?

Answer

What an interesting Question...

So after some exploring (both inward exploration bringing deep cathartic satisfaction and exploring google for MySQL documentation) and testing I can help you with this:

'AND' is a logical operator that combines two boolean tests. 'id AND text' is one single statement. Basically are both id AND text true. Try this statement to determine the truth value of your combinations:

select (id AND text),id,text from table

Now lets back up and talk about GROUP BY

GROUP BY basically gives you unique values for all values in a column. If you have a list of GROUP BY conditions example the output will be a row for each unique combination of those columns.

This is why when you list 'Group By id,text' you receive all unique combinations, omitting the duplicated pair b,b.

So as Paul mentions grouping by 'id AND text' whatever they are is basically grouping by TRUE or FALSE. That means that you will receive all unique combinations of TRUE or FALSE. From your test above, where you looked at the values of (id AND text) you can see that the first result of each result appears.

GROUP BY x AND y is rare, it would need a very special circumstance for that query to make sense. GROUP BY x, y is super common and can be used with AGGREGATE functions to give data per unique sets data for given columns.

Comments