GROUP BY - do not group NULL

mysql sql group-by
slik picture slik · Jan 3, 2011 · Viewed 82.1k times · Source

I'm trying to figure out a way to return results by using the group by function.

GROUP BY is working as expected, but my question is: Is it possible to have group by ignore NULL field. So that it does not group NULLs together because I still need all the rows where the specified field is NULL.

SELECT `table1`.*, 
    GROUP_CONCAT(id SEPARATOR ',') AS `children_ids`
FROM `table1` 
WHERE (enabled = 1) 
GROUP BY `ancestor`

So now lets say I have 5 rows and ancestor field is NULL, it returns me 1 row....but I want all 5.

Answer

bot403 picture bot403 · Jan 3, 2011

Perhaps you should add something to the null columns to make them unique and group on that? I was looking for some sort of sequence to use instead of UUID() but this might work just as well.

SELECT `table1`.*, 
    IFNULL(ancestor,UUID()) as unq_ancestor
    GROUP_CONCAT(id SEPARATOR ',') AS `children_ids`
FROM `table1` 
WHERE (enabled = 1) 
GROUP BY unq_ancestor

Related questions

Retrieving the last record in each group - MySQL

There is a table messages that contains data as shown below: Id Name Other_Columns ------------------------- 1 A A_data_1 2 A A_data_2 3 A A_data_3 4 B B_data_1 5 B B_data_2 6 C C_data_1 If I run a query select * …

Read More
MySQL Query GROUP BY day / month / year

Is it possible to make a simple query to count how many records I have in a determined period of time like a year, month, or day, having a TIMESTAMP field, like: SELECT COUNT(id) FROM stats WHERE record_date.…

Read More
Error related to only_full_group_by when executing a query in MySql

I have upgraded my system and have installed MySql 5.7.9 with php for a web application I am working on. I have a query that is dynamically created, and when run in older versions of MySql it works fine. Since upgrading …

Read More