rbennett485 rbennett485 - 4 months ago 13
MySQL Question

MySQL GROUP BY without crossovers from another column

I have a table

my_table
, with fields
group_id
,
region
,
value
. I want to select the chains of values from each group, but only if
region = US
for all records in the group.

The query I initially tried is

SELECT
GROUP_CONCAT(
value
ORDER BY DATE DESC SEPARATOR '<-'
) AS value_chain
FROM my_table
WHERE region = 'US'
GROUP BY (group_id)


This is incorrect, as if there is a
US
record and a
CA
record in the same chain, the
CA
record will be omitted before the
GROUP BY
, and the
US
record will be returned as a group of size 1. In this case I don't want to return the chain at all.

Is there a good way of achieving this?
For performance reasons I would rather not build all chains from all regions and then filter if at all possible.

Answer

Try this:

SELECT GROUP_CONCAT(value ORDER BY DATE DESC SEPARATOR '<-') AS value_chain
FROM my_table
GROUP BY group_id
HAVING SUM(region <> 'US') = 0

This will filter out groups having at least one non-'US' value.

Visual

-- drop table if exists my_table;
create table my_table
(   group_id int not null, 
    region varchar(20) not null, 
    value int not null,
    date date not null
);
-- truncate table my_table;
insert my_table values
(1,'US',100,'2016-01-07'),
(1,'CAD',200,'2016-01-06'),
(2,'US',700,'2016-02-07'),
(3,'CAD',666666,'2016-01-07'),
(4,'US',1200,'2016-02-04'),
(4,'US',1400,'2016-02-03');


SELECT GROUP_CONCAT(value ORDER BY DATE DESC SEPARATOR '<-') AS value_chain 
FROM my_table 
GROUP BY group_id 
HAVING SUM(region <> 'US') = 0 
+-------------+
| value_chain |
+-------------+
| 700         |
| 1200<-1400  |
+-------------+