combining rows that contain two separate fields of the same value

Question

I have a table formatted like so:

title            source              subject   
Bill hits Fred   newspaper 1/1/17    Bill     
Bill hits Fred   newspaper 1/1/17    Fred
Bill hits Fred   newspaper 1/1/17    Violence
Mary likes pie   newspaper 1/4/17    Mary
Mary likes pie   newspaper 1/4/17    Pie 
Mary likes pie   newspaper 1/4/17    Apple
John dies        newspaper 1/4/17    John 
John dies        newspaper 1/4/17    Obituary
...

What I need to achieve is a query that finds all rows that have the same value for the title and source fields and combines into one record concatenating the subject field. ie the output for the above data would be:

title            source              subject   
Bill hits Fred   newspaper 1/1/17    Bill, Fred, Violence     
Mary likes pie   newspaper 1/4/17    Mary, Pie, Apple
John dies        newspaper 1/4/17    John, Obituary
...

I figure I need to GROUP_CONCAT but am unsure of the exact syntax to compare title and source across all rows. Something along the lines of :

select title, source, GROUP_CONCAT(subject) from mytable
WHERE

??? <<-- not sure how to word the "title=title and source=source"

SOLUTION: I was missing GROUP BY:

SELECT title, source, GROUP_CONCAT(subject) from mytable GROUP BY title, source

You need an appropriate `GROUP BY`. – PM 77-1 Jan 17 '17 at 22:34 — PM 77-1, Jan 17 '17 at 22:34

score 2 · Accepted Answer · answered Jan 17 '17 at 22:34

2

Use:

SELECT title, source, GROUP_CONCAT(subject) from mytable GROUP BY title, source

answered Jan 17 '17 at 22:34

Inpego

2,657
13
14

Ah...GROUP BY, I feel silly. Thanks so much – Fish Jan 17 '17 at 23:12

score 1 · Answer 2 · answered Jan 17 '17 at 23:04

1

try this

select title,source,group_concat(subject) as subject from tbl5 group by title;

check on fiddle

answered Jan 17 '17 at 23:04

denny

2,084
2
15
19

combining rows that contain two separate fields of the same value

2 Answers2