MySQL Group By And Skip Grouping On Null Values

Question

select * from dc_deal group by collection_id

In collection_id column i have values (1,3,3,4,4,5,NULL,NULL). Above query will return rows with (1,2,3,4,NULL) but i want to skip grouping on NULL value and need result like (1,2,3,4,NULL,NULL)

Answer 1

Try this:

SELECT * FROM dc_deal 
GROUP BY collection_id, 
case WHEN collection_id IS NULL THEN ID ELSE 0 END

Replace ID with another column in the table to group by.

See an example in SQL Fiddle.

Answer 2

If we have a unique column (or set of columns) in the table, then we can add another expression to the GROUP BY.

The expression needs to return a unique value for each row when collection_id is null. Otherwise, it returns a constant.

Assuming we have a unique id column in the table, then we can do something like this:

... GROUP BY collection_id, IF(collection_id IS NULL, id, 0)

That second expression in the GROUP BY returns a constant value 0 when collection_id is not null. But it returns a unique value for each row when collection_id is null.

Note that id here is just a reference to a column that is defined to be unique within the table. The PRIMARY KEY is a good candidate. If we don't have a unique index on a single column, then we can repeat this same type of expression for each column in our unique constraint, or for any set of expressions that is guaranteed to be unique on each row.

... GROUP BY collection_id
           , IF(collection_id IS NULL, col1, '')
           , IF(collection_id IS NULL, col2, NULL)
           , IF(collection_id IS NULL, col3, collection_id)

Alternatively, we can use an expression generates a unique value:

... GROUP BY IFNULL(collection_id,UUID())