3

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
1
  • 2
    You need an appropriate GROUP BY. Commented Jan 17, 2017 at 22:34

2 Answers 2

2

Use:

SELECT title, source, GROUP_CONCAT(subject) from mytable GROUP BY title, source
Sign up to request clarification or add additional context in comments.

1 Comment

Ah...GROUP BY, I feel silly. Thanks so much
1

try this

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

check on fiddle

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.