May 22, 2013
Merging columns if a column has a count > 1
Lotier’s Question:
I’m trying to create a column that is merged column if the count of column_1 is greater than 1, and just the value of one of the columns if 1, when matching to column_1. So far I can get the column to merge properly, but it only returns the first row back, not all of them.
Example Table
id, col_1, col_2, col_3
---------------------------
1, 'Name1', 'Value', '1',
2, 'Name2', 'Value', '2',
3, 'Name2', 'Value', '3',
4, 'Name3', 'Value2', '1',
5, 'Name2', 'Value3', '1';
What I would like back
2, Name2, Value 2
3, Name2, Value 3
5, Name2, Value3
What I’ve attempted so far
SELECT id,
IF (count(col_2) > 1, concat(col_2, ' ', col_3), col_2) as merge
FROM mytable
where col_1 = 'Name2'
which only returns ‘2, Value 2’ Thanks for the help. I’ve messed with it at http://sqlfiddle.com/#!2/9ce7e/7
count() is a aggregate function, thus it return single row as output.
You can user the following query to make sure col_2 has some value before concatenating
SELECT id,
IF (col_2 <> "" , concat(col_2, ' ', col_3), col_2) as merge
FROM mytable
where col_1 = 'Name2'
