rbennett485 rbennett485 - 4 months ago 22
SQL Question

MySQL GROUP BY - exclude groups where values in a column differ within the group

I have a query like the following which returns concatenated chains of dates for each group:

myTable
has columns
group_id
,
date
, and
region


SELECT
GROUP_CONCAT(
date
ORDER BY DATE DESC SEPARATOR '<-'
) AS date_chain
FROM myTable
GROUP BY group_id


However I want to omit any group where there is more than one
region
value within that group. Is there a straightforward way to achieve this?

e.g. If we had

group_id | date | region
________________________
1 | 2012 | US
1 | 2013 | UK
2 | 2010 | US
2 | 2014 | US


we should get
2014<-2010
returned, but not
2013<-2012
because group 1 changes region

Answer
SELECT
GROUP_CONCAT(
             date
             ORDER BY DATE DESC SEPARATOR '<-'
) AS date_chain
FROM myTable
GROUP BY group_id
HAVING COUNT(DISTINCT region)<2