CODEWITHSUNDEEP
phpmysqlcodeigniter
Talha Rehman
Talha Rehman

Reputation: 55

Marking rows of table as duplicate if one column has duplicate values

I have a table caller which keeps track of all the calls I receive. The columns are "phone" , "time" , and "ID" which I then show on a web page. Now I want to show (Repeat) with phone numbers that have called twice or more. Right Now I am using the following query. 

SELECT `Phone`, `Id`, `time` 
FROM callers
GROUP BY phone
ORDER BY `time` DESC

this query returns the phones only once which have called twice. I want to show those phones as many time as they have called. Just want a (R) written in front of every phone that has been repeated. I am using Codeigniter framework and PHP.

Example (What I want)

ID       Phone          Time
1        789654 (R)      3:40
2        789654 (R)      3:30
3        123456          2:00
4        012344          1:00

What the above query gives me

ID       Phone          Time
1        789654 (2)      3:40
2        123456          2:00
3        012344          1:00

Upvotes: 1

Views: 91

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269703

You need to calculate the information about repetition separately from listing the numbers. Here is a method using LEFT JOIN:

SELECT c.*, cc.RepeatFlag
FROM callers c LEFT JOIN
     (SELECT `Phone`, 'R' as RepeatFlag
      FROM callers
      GROUP BY phone
      HAVING cnt > 1
     ) cc
     ON cc.phone = c.phone
ORDER BY `time` DESC ;

This uses a bit of a trick. The subquery only returns the the phones with duplicates. For these, it sets the RepeatFlag. For non-matches, this is NULL. You can choose whatever columns you want from callers for the rest of the columns.

Upvotes: 2

Harish Lalwani
Harish Lalwani

Reputation: 774

This query will return no of calls per phone no. 

 SELECT `Phone`, `Id`, `time`, count(Id) as no_of_calls 
 FROM callers
 GROUP BY phone
 ORDER BY `time` DESC

Or

 SELECT `Phone`, `Id`, `time`, IF(count(Id)>1, 1, 0) as repeat 
 FROM callers
 GROUP BY phone
 ORDER BY `time` DESC

Upvotes: 0

Related Questions