CODEWITHSUNDEEP
mysqlsqlgroup-bygroup-concat
fatherazrael
fatherazrael

Reputation: 5957

MYSQL: How to append Null value using GROUP_CONCAT and Separator using groupby?

How to display Null using Group Concat and Separator

select sr.aID,
 GROUP_CONCAT(p.GENDER SEPARATOR ',') as Gender,
 GROUP_CONCAT(sr.FANS SEPARATOR ',') as FA,
from RELATION sr, Person p where RELATION _TYPE = 'FATHER'
and  sr.bID= p.ID group by sr.subjektA_ID;

It returns following value for FA

1 | MALE, FEMALE | YES, NO
2 | FEMALE, MALE | NO, YES
3 | FEMALE, MALE | YES
4 | FEMALE, MALE | NO

I want it to display Null value i.e 

3 | FEMALE, MALE | YES, NULL
4 | FEMALE, MALE | NULL, NO

How to achieve same using GROUP Concat and Separator along together

Upvotes: 1

Views: 125

Answers (2)

dimacros
dimacros

Reputation: 47

select sr.aID,
 GROUP_CONCAT( IFNULL(p.GENDER, 'NULL') ) as Gender,
 GROUP_CONCAT( IFNULL(sr.FANS, 'NULL') ) as FA,
from RELATION sr, Person p where RELATION _TYPE = 'FATHER'
and  sr.bID= p.ID group by sr.subjektA_ID;

Annotations: GROUP_CONCAT by default separates the fields by "," so, if you like you can omit it, IFNULL (column, 'default_text'), this replaces all the null values ​​with one that you choose by default.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520908

Try replacing those NULL values with the string literal 'NULL':

SELECT
    sr.aID,
    GROUP_CONCAT(p.GENDER) AS Gender,
    GROUP_CONCAT(COALESCE(sr.FANS, 'NULL')) AS FA
FROM RELATION sr
INNER JOIN Person p
    ON sr.bID = p.ID
WHERE
    RELATION _TYPE = 'FATHER'
GROUP BY
    sr.aID;

GROUP_CONCAT, like most of the aggregate functions, ignore NULL values. But for presentation purposes, you only want to see the string 'NULL', so the above suggestion should work.

Note I also replaced your old style joins with explicit joins. Also, you should select the same column which you used to aggregate.

Upvotes: 2

Related Questions