MySQL – Using Multiple GROUP_CONCATs with WHERE Clause

group bygroup-concatenationMySQLselect

In a table as

id    name      type           info
1     BMW       car            yes
2     Reno      car            no
3     IBM       electronics    no
4     Sony      electronics    yes
5     Mazda     car            yes

I use GROUP_CONCAT to get the list of each type, but I want to separate the concatenated column to multiple columns categorized by the column info. It should be something like

SELECT type,
       GROUP_CONCAT(name) ORDER BY id ASC SEPARATOR ' ') AS list_with_info
       GROUP_CONCAT(name) ORDER BY id ASC SEPARATOR ' ') AS list_without_info
       FROM table1 GROUP BY type

How can I introduce WHERE clause or using other approach to return multiple concatenated columns?

Best Answer

SELECT type,
       GROUP_CONCAT( CASE WHEN info = 'yes' THEN name ELSE NULL END 
                     ORDER BY id ASC SEPARATOR ' ') AS list_with_info,
       GROUP_CONCAT( CASE WHEN info = 'no' THEN name ELSE NULL END 
                     ORDER BY id ASC SEPARATOR ' ') AS list_without_info      
FROM table1
GROUP BY type ;

Tested at SQL-Fiddle: test-1

If you wanted the results in two rows instead of one, it would be easier, just GROUP BY both type, info:

SELECT 
    type, info,
    GROUP_CONCAT( name ORDER BY id ASC SEPARATOR ' ')
      AS list     
FROM table1
GROUP BY type
       , info ;

This can also be used to provide the one-row-per-type format you want:

SELECT
    type,
    MIN( CASE WHEN info = 'yes' THEN list END )
      AS list_with_info,
    MIN( CASE WHEN info = 'no' THEN list END )
      AS list_without_info    
FROM
    ( SELECT 
          type, info,
          GROUP_CONCAT( name ORDER BY id ASC SEPARATOR ' ')
            AS list     
      FROM table1
      GROUP BY type
             , info 
    ) AS grp 
GROUP BY type ;

Tested at SQL-Fiddle: test-2

The above two queries would benefit from an index on (type, info, name)

The following would benefit from an index on (info, type, name):

SELECT
    dt.type,
    grpy.list  AS list_with_info,
    grpn.list  AS list_without_info    
FROM
    ( SELECT DISTINCT type
      FROM table1
    ) AS dt
  LEFT JOIN
    ( SELECT 
          type,
          GROUP_CONCAT( name ORDER BY id ASC SEPARATOR ' ')
            AS list     
      FROM table1
      WHERE info = 'yes'
      GROUP BY type 
    ) AS grpy
      ON grpy.type = dt.type
  LEFT JOIN
    ( SELECT 
          type,
          GROUP_CONCAT( name ORDER BY id ASC SEPARATOR ' ')
            AS list     
      FROM table1
      WHERE info = 'no'
      GROUP BY type 
    ) AS grpn
      ON grpn.type = dt.type ;

Tested at SQL-Fiddle: test-3

Related Question