how to remove duplicate data using mysql query

Question

I have my data is like this in table.

 name  day1  day2 day3 
 Anand  P     NA   NA 
 Anand  NA    P    NA 
 Anand  NA   NA  P 


I want result like this

name  day1 day2 day3
Anand P    P     P

Group by clause is giving only first row data. Please suggest me mysql query. you can assume table name as att_table.

if you used 1/0 instead of P / NA, you could then use sum() with group by, which would give you the result you want. — Tuncay Göncüoğlu
– Tuncay Göncüoğlu, Commented Apr 7, 2016 at 10:43
what are the values contain in the fields?? Int or string??? — Murad Hasan
– Murad Hasan, Commented Apr 7, 2016 at 10:44
Group by clause is giving only first row data What aggregation function did you use with GROUP BY? — Barmar
– Barmar, Commented Apr 7, 2016 at 10:47
Is there a difference between this question and your last question? — Barmar
– Barmar, Commented Apr 7, 2016 at 10:56

Barmar · Accepted Answer · 2016-04-07 10:46:18Z

4

You can use NULLIF to replace the NA value with NULL, and then use MAX() to get the non-null value within each group.

SELECT name, MAX(NULLIF(day1, 'NA')) AS day1, MAX(NULLIF(day2, 'NA')) AS day2, MAX(NULLIF(day3, 'NA')) AS day2
FROM att_table
GROUP BY name

answered Apr 7, 2016 at 10:46

Barmar

789k57 gold badges555 silver badges669 bronze badges

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

Comments

Adrien Brunelat · Accepted Answer · 2016-04-07 10:55:17Z

0

Try this:

SELECT
  name,
  GROUP_CONCAT(day1),
  GROUP_CONCAT(day2),
  GROUP_CONCAT(day3)
FROM att_table
GROUP BY name;

If 'NA' is a string and not SQL's NULL value, try this instead:

SELECT
  name,
  GROUP_CONCAT(IF(day1 = 'NA', NULL, day1)),
  GROUP_CONCAT(IF(day2 = 'NA', NULL, day2)),
  GROUP_CONCAT(IF(day3 = 'NA', NULL, day3))
FROM att_table
GROUP BY name;

edited Apr 7, 2016 at 10:55

answered Apr 7, 2016 at 10:44

Adrien Brunelat

4,6704 gold badges35 silver badges44 bronze badges

6 Comments

Barmar Over a year ago

missing GROUP BY

Adrien Brunelat Over a year ago

Nope, the GROUP BY clause serves no purpose here.

Barmar Over a year ago

If you don't use GROUP BY, you'll combine all the rows for all the different users.

Adrien Brunelat Over a year ago

Then use where name = Anand

Barmar Over a year ago

Why do you think he only wants the result for one user, not to do it for all the users?

|

Collectives™ on Stack Overflow

how to remove duplicate data using mysql query

2 Answers 2

Comments

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related