Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Databases

Eric Ewers
Eric Ewers
13,976 Points
Posted by Eric Ewers
Eric Ewers
13,976 Points

MySQL SELECT - Get rid of duplicates in column

I have an attendee table with companies and corresponding titles that belong to those companies. These are in two separate columns. I need to list all of the titles under the company names, but don't want the same company to show up twice.

My current statement:

SELECT Title, Company FROM attendees ORDER BY Company ASC

The result:

Carnegie Mellon University Project Manager

Carnegie Mellon University Director of Scientific Computing for MCS

Carnegie Mellon University Associate Director, CyLab

Carnegie Mellon University Manager of Telecommunications

Carnegie Mellon University Dean

What I want the result to be:

Carnegie Mellon University Project Manager, Director of Scientific Computing for MCS, Associate Director, CyLab, Manager of Telecommunications, Dean

I know that DISTINCT will get rid of duplicates, but how can I keep the titles to those duplicate companies?

Is this possible?

2 Answers

Jason Anello
PLUS
Jason Anello
Courses Plus Student 94,610 Points
Jason Anello
Jason Anello
Courses Plus Student 94,610 Points

Hi Eric,

I think you'll probably need to add a GROUP BY Company clause to group all the companies together and then use the GROUP_CONCAT aggregate function on the Title column to group all the titles.

SELECT Company, GROUP_CONCAT(DISTINCT Title
                             ORDER BY Title ASC
                             SEPARATOR ', ')
FROM attendees
GROUP BY Company
ORDER BY Company ASC;

You can check the options here http://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-concat

I included them all here but you can take out the ones you don't need. The default separator is a comma only. So if you want a comma and space you'll have to include the separator option.

This is just a suggestion but since your titles can have commas in them you might want to use a different separator. It could get confusing on a long list of titles whether Cylab belongs to what came before or what comes after as an example.

Christian Andersson
Christian Andersson
8,712 Points
Christian Andersson
Christian Andersson
8,712 Points

Yes, this is definitely possible! You can do this with GROUP_CONTACT. Try this:

SELECT Title, GROUP_CONCAT(Company) FROM attendees

the ORDER BY is probably not needed here.