CODEWITHSUNDEEP
mysqlsqlgroup-concat
imapotatoe123
imapotatoe123

Reputation: 696

SELECT GROUP_BY and GROUP_CONCAT same values in a table only if one of the values has a NULL field

I have a table full of names and addresses. I want to combine (group_concat) all the rows that have the same values for city state and name, but only if one of the addresses is a NULL value. For example I might have this table: 

name     |    addr    |  city  | state  | phone 

jon snow | 123 got st |  la    | CA     | 1234567890
jon snow | NULL       |  la    | CA     | 1225556789
walker   | 777 diff ln|  la    | CA     | 4556678899
walker   |456 crown rd|  la    | CA     | 33344455566

I would like the outcome to be:

name     |    addr    |  city  | state  | phone

jon snow | 123 got st |  la    | CA     | 1234567890,1225556789
walker   | 777 diff ln|  la    | CA     | 4556678899
walker   |456 crown rd|  la    | CA     | 33344455566

Currently I am using the following command: 

SELECT name, group_concat(distinct(addr)) as addr, city, state, group_concat(distinct phone) as phone from got_table group by name, city, state having count(*) >1;

This ends up combining all addresses with the same name, city, state even if there are two non-null addresses. The outcome of the above statement looks like below: 

name     |    addr                  |  city  | state  | phone

jon snow | 123 got st               |  la    | CA     | 1234567890,1225556789
walker   | 777 diff ln, 456 crown rd|  la    | CA     | 4556678899, 33344455566

Is there a way to only combine if one of the row's addresses are NULL and only perform this group_concat if there are only 2 rows that match.

Upvotes: 1

Views: 1494

Answers (1)

D-Shih
D-Shih

Reputation: 46219

You can try to group by CASE WHEN with subquery to make it.

check addr column whether NULL get.

  1. addr IS NULL get MAX value or your expect group by concat value.
  2. addr IS NOT NULL get addr

look like this.

CREATE TABLE got_table(
   name varchar(50),
   addr varchar(50),
  city varchar(50),
    state varchar(50),
    phone varchar(50)
); 


insert into got_table values ('jon snow','123 got st'  ,'la', 'CA', '1234567890');
insert into got_table values ('jon snow',NULL          ,'la', 'CA', '1225556789');
insert into got_table values ('walker','777 diff ln' ,'la', 'CA'  , '4556678899');
insert into got_table values ('walker','456 crown rd','la', 'CA'  , '33344455566');

Query 1:

SELECT name,
       case when addr is null then
       (
         SELECT MAX(tt.addr) 
         FROM got_table tt
         WHERE t1.name = tt.name 
         and t1.city = tt.city 
         and t1.state = tt.state
       ) else addr end as address,
       city, 
       state, 
       group_concat(distinct phone) as phone 
from got_table t1
group by name, city, state,address

Results:

|     name |      address | city | state |                 phone |
|----------|--------------|------|-------|-----------------------|
| jon snow |   123 got st |   la |    CA | 1225556789,1234567890 |
|   walker | 456 crown rd |   la |    CA |           33344455566 |
|   walker |  777 diff ln |   la |    CA |            4556678899 |

Upvotes: 2

Related Questions