MYSQL using AND in group by statement instead of commas

Question

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 1

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.

Answer 2

AND is a boolean operator. It returns TRUE (1) if both values are TRUE. It returns FALSE (0) if one value is FALSE. If a value is not numeric it will be converted first. GROUP BY id AND text will only return two rows, because there are only two possible values (TRUE and FALSE).

It could make sense grouping by a combination of two boolean flags.

Example:

SELECT 
    CASE WHEN (u.conditions_accepted AND u.payment_made)
        THEN 'valid users'
        ELSE 'not valid users'
    END AS `group`,
    COUNT(u.user_id) AS num_users
FROM users u
GROUP BY (u.conditions_accepted AND u.payment_made)