Combine multiple distinct values

Question

I have a table with the following structure:

| animal | color |
|--------|-------|
| dog    | black |
| dog    | white |
| cat    | white |
| dog    | black |
| mouse  | grey  |

I now want to get the distinct values of each column.

Just doing:

SELECT DISTINCT animal, color from tab1

Would just omit the 2nd occurrence of dog - black:

| animal | color |
|--------|-------|
| dog    | black |
| dog    | white |
| cat    | white |
| mouse  | grey  |

But what I want is some structure that looks as follows:

animal: dog, cat, mouse
color: black, white, grey

My approach would just be to perform several SELECT queries:

 - SELECT DISTINCT animal from tab1
 - SELECT DISTINCT color from tab1

And then just combine those results into an array with PHP.

But is there a quicker way, maybe with just one query?

first up why not clean your data? You have dog,black repeated — e4c5
– e4c5, Commented Nov 25, 2016 at 13:54

Praveen Kumar Purushothaman · Accepted Answer · 2016-11-25 13:54:12Z

6

Yes, your second approach is right, but you can do it using SQL:

SELECT DISTINCT `animal` from `tab1` UNION SELECT DISTINCT `color` from `tab1`

A good thing about UNION is, it already filters your values. So, you can get rid of DISTINCT leaving you with:

SELECT `animal` from `tab1` UNION SELECT `color` from `tab1`

But in this case, you won't be able to differentiate which is from color and which is from animal. You may add a separator SELECT and use it to differentiate.

answered Nov 25, 2016 at 13:54

Praveen Kumar Purushothaman

167k27 gold badges215 silver badges261 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Evgenij Reznik Over a year ago

Thank you, but could you please describe what you mean by separator SELECT? I really need to differentiate which groups (animal or color) the values belong to.

Praveen Kumar Purushothaman Over a year ago

@user1170330 now this would give you all the list of colours and animals combined in a single column right, so to add a separator, add one more UNION in the middle, like UNION SELECT '-----' so it gives you a differentiation. Let me know if it works. :D

Praveen Kumar Purushothaman Over a year ago

Also, the problem or the advantage in your current situation is, each colour is associated with the particular animal, so it is not possible to split them both unless you use two queries. :)

Seb Over a year ago

I would not advice using a seperator-select, but using a second column in your query that states the table name. Something like: SELECT 'animal', animal from tab1 UNION SELECT 'color', color from tab1

AdrianES · Accepted Answer · 2016-11-25 14:15:17Z

0

U can group them by the two columns with this sql:

SELECT animal, color FROM tab1 GROUP BY animal, color

EDIT:

To test my statement i created a mysql table with this SQL:

CREATE TABLE animals ( ID int(11) NOT NULL AUTO_INCREMENT,
name varchar(45) DEFAULT NULL, color varchar(45) DEFAULT NULL,
PRIMARY KEY (ID) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

I populate with this values:

1 dog white

2 dog black

3 dog white

4 cat white

When i run this SQL: "SELECT name, color FROM animals GROUP BY name, color"

I got this results:

cat white

dog black

dog white

If u want to get who has duplicates you can run SQL like this "SELECT name, color, COUNT(ID) as number FROM animals GROUP BY name, color HAVING number > 1" which gives me this result:

dog white 2

Hope i helped you.

edited Nov 25, 2016 at 14:15

answered Nov 25, 2016 at 13:58

AdrianES

6803 gold badges13 silver badges31 bronze badges

2 Comments

B001ᛦ Over a year ago

Are you sure? Look at Aurios's comment

AdrianES Over a year ago

Yes i am sure, this will give the same result as distinct by two columns plus u can make something like this SQL: "SELECT animal, color, COUNT(ID) as count_no FROM tab1 GROUP BY animal, color" to count how many occurrence in the table have duplicates. Btw I always check before posting :)

Collectives™ on Stack Overflow

Combine multiple distinct values

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related