GROUP BY - do not group NULL

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.

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

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.

GROUP BY IFNULL(required_field, id)

SELECT table1.*, 
    GROUP_CONCAT(id SEPARATOR ',') AS children_ids
FROM table1
WHERE (enabled = 1) 
GROUP BY ancestor
       , CASE WHEN ancestor IS NULL
                  THEN table1.id
                  ELSE 0
         END

Maybe faster version of previous solution in case you have unique identifier in table1 (let suppose it is table1.id) :

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

To union multiple tables and group_concat different column and a sum of the column for the (unique primary or foreign key) column to display a value in the same row

select column1,column2,column3,GROUP_CONCAT(if(column4='', null, column4)) as 
column4,sum(column5) as column5
from (
      select column1,group_concat(column2) as column2,sum(column3 ) as column3,'' as 
      column4,'' as column5
      from table1 
      group by column1

      union all

      select column1,'' as column2,'' as column3,group_concat(column4) as 
      column4,sum(column5) as column5
      from table 2 
      group by column1
     ) as t
     group by column1