GROUP BY - do not group NULL

Question

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 a group by ignoring the 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 let's say I have 5 rows and the ancestor field is NULL, it returns me 1 row....but I want all 5.

Answer 1

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 

Answer 2

When grouping by column Y, all rows for which the value in Y is NULL are grouped together.

This behaviour is defined by the SQL-2003 standard, though it's slightly surprising because NULL is not equal to NULL.

You can work around it by grouping on a different value, some function (mathematically speaking) of the data in your grouping column.

If you have a unique column X then this is easy.

---

Input

X      Y ------------- 1      a 2      a 3      b 4      b 5      c 6      (NULL) 7      (NULL) 8      d 

---

Without fix

SELECT GROUP_CONCAT(`X`)   FROM `tbl`  GROUP BY `Y`; 

Result:

GROUP_CONCAT(`foo`) ------------------- 6,7 1,2 3,4 5 8 

---

With fix

SELECT GROUP_CONCAT(`X`)   FROM `tbl`  GROUP BY IFNULL(`Y`, `X`); 

Result:

GROUP_CONCAT(`foo`) ------------------- 6 7 1,2 3,4 5 8 

---

Let's take a closer look at how this is working

SELECT GROUP_CONCAT(`X`), IFNULL(`Y`, `X`) AS `grp`   FROM `tbl`  GROUP BY `grp`; 

Result:

GROUP_CONCAT(`foo`)     `grp` ----------------------------- 6                       6 7                       7 1,2                     a 3,4                     b 5                       c 8                       d 

---

If you don't have a unique column that you can use, you can try to generate a unique placeholder value instead. I'll leave this as an exercise to the reader.