CODEWITHSUNDEEP
mysql
Shifty
Shifty

Reputation: 13

MySQL compare name and address

Let's say I have a table like this:

name | last name | postal code | street+no
------------------------------------------
max  | tester    | 12345       | teststreet 1
mia  | tester    | 12345       | teststreet 1
frank| stein     | 54321       | streettest 2

and I want to show up my query result like this:

name   | last name | postal code | street+no
----------------------------------------------
family | tester    | 12345       | teststreet 1
frank  | stein     | 54321       | streettest 2

So it should test if there are lastnames with same address and CONCAT them into 1 row with family as name

I did this before, but it just shows me the table without first names, and i don't know how to compare them right...

SELECT DISTINCT `last name`,`postal code`, `street+no` 
  FROM `users` 
 ORDER BY `last name`

Thanks in advance :)

Upvotes: 1

Views: 170

Answers (1)

fancyPants
fancyPants

Reputation: 51928

Pretty self explaining:

SELECT
CASE WHEN COUNT(*) > 1 THEN 'family' ELSE name END AS name,
last_name,
postal_code,
street_no
FROM your_table
GROUP BY 
last_name,
postal_code,
street_no

Upvotes: 1

Related Questions