Question 21108 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL: result of GROUP BY into columns

I have a dogs table that looks like this:

create table dogs (name char(10), breed, char(20), age int);

insert into dogs (name, breed, age) values ('A', 'Breed 1', 1);
insert into dogs (name, breed, age) values ('B', 'Breed 2', 2);
insert into dogs (name, breed, age) values ('C', 'Breed 3', 3);
insert into dogs (name, breed, age) values ('D', 'Breed 1', 4);
insert into dogs (name, breed, age) values ('E', 'Breed 2', 2);
insert into dogs (name, breed, age) values ('F', 'Breed 3', 5);
insert into dogs (name, breed, age) values ('G', 'Breed 1', 1);

enter image description here

I need to select names of dogs for which their respective breeds and ages are equal, so the output should be the following:

name1    name2
A        G
B        E

because dogs A and G have the same breed (Breed 1) and age (1), and dogs B and E have the same breed (Breed 2) and age (2).

I know how to find out the names of the dogs that should be in the output:

select name from dogs where (breed, age) in (select breed, age from (select breed, age, count(*) as n from dogs group by breed, age having n > 1) as a);

enter image description here

However I’m lost as to how to convert it to a desired output with two columns.

Tags:

3

Answers


  1. 0

    That is usually done with a self join:

    select D1.name, D2.name
from dogs d1
     inner join
     dogs d2
     on d1.breed=d2.breed
     and d1.age=d2.age
     and d1.name<d2.name

    the d1.name>d2.name serves two purposes: 1. to prevent the row to match itself, and 2. to make sure that we get only one row per pair (not (A,G) and (G,A), for example).

    Login or Signup to reply.
  2. 0

    UseGROUP_CONCAT() to distinguish diffrent names

    select 
GROUP_CONCAT(distinct name) name
from dogs
group by 
breed
,age 
having GROUP_CONCAT(name) like '%,%'

    the result is

    name
    A,G
    B,E

    then use SUBSTRING_INDEX and subquery to change above result to columns

    SELECT 
SUBSTRING_INDEX(Name, ',', 1) name1    
,SUBSTRING_INDEX(Name, ',', -1) name2   
From(
select 
GROUP_CONCAT(distinct name) Name
from dogs
group by 
breed
,age 
having GROUP_CONCAT(name) like '%,%') a
    name1 name2
    A G
    B E

    dbfiddle

    Login or Signup to reply.
  3. 0 
    SELECT 
 d1.name,d1.breed, d1.age, d2.name, d2.breed, d2.age
   FROM dogs  d1 
    JOIN dogs  d2
      ON d1.breed=d2.breed AND d1.age = d2.age AND d1.name != d2.name
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search