GROUP CONCAT some distinct some not

Question

I have a table

company   invest_type  date  round
----------------------------------
A         regular      2011  
A         regular      2011  
A         regular      2012  
A         special      2010  abcd
A         special      2010  abcd

B         regular      2011  
B         regular      2011  
B         regular      2012  
B         special      2010  cdcd
B         special      2010  zzzz

C         regular      2012  
C         regular      2012  
C         special      2010  
C         special      2010  

I want to display them like this

company  dates
A        2010,2011,2011,2012
B        2010,2010,2011,2011,2012
C        2010,2012,2012

That is, special investments dates are deduped(usually assigned rounds) but regular investments are not.

I've tried `GROUP_CONCAT(DISTINCT date,invest_type) but it doesn't come close. Basically I want to grab distinct date values from 'date' as long as round is not 'null' in that case I want repeated values. If round is present, dedupe based on round, if not assume all special investments are the same round and dedupe them.

Answer 1

Use a subquery to replace the null rounds in regular investments with a counter, so that the rows will by unique, and then use SELECT DISTINCT to de-dupe everything else. Then use GROUP_CONCAT on this.

SELECT company, GROUP_CONCAT(date ORDER BY date) AS dates
FROM (
    SELECT DISTINCT
               company, date,
               CASE WHEN round IS NOT NULL THEN round
                    WHEN invest_type = 'regular' THEN @counter := @counter + 1
                    ELSE null
               END AS round
    FROM investments
    CROSS JOIN (SELECT @counter := 0) AS var) AS x
GROUP BY company

DEMO