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, Apr 07 '16 at 10:43
what are the values contain in the fields?? Int or string??? — Murad Hasan, Apr 07 '16 at 10:44
**Group by clause is giving only first row data** What aggregation function did you use with `GROUP BY`? — Barmar, Apr 07 '16 at 10:47
_Closesly_ related to this http://stackoverflow.com/q/36472820/5830574 — PerlDuck, Apr 07 '16 at 10:49
@Barmar I guess the OP used `max`. At least he said so in [this comment](http://stackoverflow.com/questions/36472820/how-to-remove-duplicates-in-mysql-query#comment60557659_36473225) — PerlDuck, Apr 07 '16 at 10:52
Is there a difference between this question and your last question? — Barmar, Apr 07 '16 at 10:56

score 4 · Answer 1 · answered Apr 07 '16 at 10:46

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

Adrien Brunelat · Answer 2 · 2016-04-07T10:55:17.850

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 07 '16 at 10:55

answered Apr 07 '16 at 10:44

Adrien Brunelat

4,492
4
29
42

missing `GROUP BY` – Barmar Apr 07 '16 at 10:46
Nope, the GROUP BY clause serves no purpose here. – Adrien Brunelat Apr 07 '16 at 10:48
If you don't use `GROUP BY`, you'll combine all the rows for all the different users. – Barmar Apr 07 '16 at 10:48
Then use `where name = Anand` – Adrien Brunelat Apr 07 '16 at 10:49
Why do you think he only wants the result for one user, not to do it for all the users? – Barmar Apr 07 '16 at 10:51
Ok good point, seeing things with that angle it makes more sense. – Adrien Brunelat Apr 07 '16 at 10:54

how to remove duplicate data using mysql query

2 Answers2