CODEWITHSUNDEEP
mysqlsqlduplicatesdistinctgroup-concat
Vito Mantese
Vito Mantese

Reputation: 127

GROUP CONCAT some distinct some not

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.

Upvotes: 0

Views: 88

Answers (2)

John Bollinger
John Bollinger

Reputation: 180181

You can perform the deduplication in an inline view, and the group_concat() in an outer query, like so:

select
  company,
  group_concat(`date` order by `date` ASC separator ',') as dates
from (
  select distinct company, `date`
  from my_table
  where invest_type = 'special'
  union all
  select company, `date`
  from my_table
  where invest_type != 'special'
) dedup
group by company

Upvotes: 0

Barmar
Barmar

Reputation: 780909

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

Upvotes: 2

Related Questions