Get more records that appear more than once

Question

How can I see all the records that appear more than once per day?

I have this table:

ID   Name     Date
1    John     27.03.2010 18:17:00
2    Mike     27.03.2010 16:38:00
3    Sonny    28.03.2010 20:23:00
4    Anna     29.03.2010 13:51:00
5    Maria    29.03.2010 21:59:00
6    Penny    29.03.2010 17:25:00
7    Alba     30.03.2010 09:36:00
8    Huston   31.03.2010 10:19:00

I wanna get:

1    John     27.03.2010 18:17:00
2    Mike     27.03.2010 16:38:00
4    Anna     29.03.2010 13:51:00
5    Maria    29.03.2010 21:59:00
6    Penny    29.03.2010 17:25:00

Answer 1

This should work assuming you are using MySQL.

SELECT *
FROM `thetable`
GROUP BY DATE(`Date`)
HAVING COUNT(*) > 1

I have not tested this however, its just the first thing I can think of.

The date function takes only the date part of a DateTime (which I assumed you were using since there's a time component shown too. Reference. I have also enclosed the Date field name in backticks since Date is a reserved word in MySQL (and did the same with the table name for consistency).

Bear in mind that different RDBMSs will probably have different functions for achieving this.

Answer 2

I'm calling the table Mytable, and changed the name of Date to somedate to not use keyword:

--create table mytable(ID int,Name varchar(32), somedate datetime)
select *
from mytable
where id in (
select id
from mytable
group by convert(varchar(10), somedate, 101), id
having count(1) > 1
)

Answer 3

  Select *  
    From YourTable  
   Where ADate in (Select ADate  
                     From YourTable  
                    Group By ADate  
                   Having Count(Distinct Id) > 1  
                 )  

Answer 4

You mean, "how can I select all records for days which have more than one record?"

select *
from your_table
where trunc(date) in ( select trunc(date)
                       from your_table
                       group by trunc(date)
                       having count(*) > 1)
/

edit

Oh, you're on SQL Server. I used ORACLE's TRUNC() function, which takes a datetime and strips the time element. Apparently SQL Server doesn't have an exact equivalent but there are some workarounds.

Answer 5

You can do it as follows:

SELECT * 
FROM   theTable t1 
WHERE  t1.created_at IN 
       (SELECT   t2.created_at 
        FROM     theTable t2 
        GROUP BY created_at 
        HAVING COUNT(*) > 1)

Answer 6

UPDATE: Using SQL Server:

CREATE TABLE t1 (id int IDENTITY PRIMARY KEY, name varchar(20), date datetime);

INSERT INTO t1 VALUES('John',   '2010-03-27 18:17:00');
INSERT INTO t1 VALUES('Mike',   '2010-03-27 16:38:00');
INSERT INTO t1 VALUES('Sonny',  '2010-03-28 20:23:00');
INSERT INTO t1 VALUES('Anna',   '2010-03-29 13:51:00');
INSERT INTO t1 VALUES('Maria',  '2010-03-29 21:59:00');
INSERT INTO t1 VALUES('Penny',  '2010-03-29 17:25:00');
INSERT INTO t1 VALUES('Alba',   '2010-03-30 09:36:00');
INSERT INTO t1 VALUES('Huston', '2010-03-31 10:19:00');

SELECT  t1.id, t1.name, sub_t.date
FROM    t1
JOIN    (SELECT   DATEADD(dd, DATEDIFF(dd,0, date), 0) as date
         FROM     t1 
         GROUP BY DATEADD(dd, DATEDIFF(dd,0, date), 0) 
         HAVING   COUNT(id) > 1) sub_t ON 
                  (sub_t.date = DATEADD(dd, DATEDIFF(dd,0, t1.date), 0));

Returns:

+----+-------+---------------------+
| id | name  | date                |
+----+-------+---------------------+
|  1 | John  | 2010-03-27 00:00:00 |
|  2 | Mike  | 2010-03-27 00:00:00 |
|  4 | Anna  | 2010-03-29 00:00:00 |
|  5 | Maria | 2010-03-29 00:00:00 |
|  6 | Penny | 2010-03-29 00:00:00 |
+----+-------+---------------------+

---

Previous answer assumed MySQL:

Joining with a sub query would be one option:

CREATE TABLE t1 (id int AUTO_INCREMENT PRIMARY KEY, 
                 name varchar(20), 
                 date datetime);

INSERT INTO t1 VALUES(NULL, 'John',   '2010-03-27 18:17:00');
INSERT INTO t1 VALUES(NULL, 'Mike',   '2010-03-27 16:38:00');
INSERT INTO t1 VALUES(NULL, 'Sonny',  '2010-03-28 20:23:00');
INSERT INTO t1 VALUES(NULL, 'Anna',   '2010-03-29 13:51:00');
INSERT INTO t1 VALUES(NULL, 'Maria',  '2010-03-29 21:59:00');
INSERT INTO t1 VALUES(NULL, 'Penny',  '2010-03-29 17:25:00');
INSERT INTO t1 VALUES(NULL, 'Alba',   '2010-03-30 09:36:00');
INSERT INTO t1 VALUES(NULL, 'Huston', '2010-03-31 10:19:00');

SELECT  t1.id, t1.name, sub_t.date
FROM    t1
JOIN    (SELECT   DATE(date) as date
         FROM     t1 
         GROUP BY DATE(date) 
         HAVING   COUNT(id) > 1) sub_t ON (sub_t.date = DATE(t1.date));

Returns:

+----+-------+------------+
| id | name  | date       |
+----+-------+------------+
|  1 | John  | 2010-03-27 |
|  2 | Mike  | 2010-03-27 |
|  4 | Anna  | 2010-03-29 |
|  5 | Maria | 2010-03-29 |
|  6 | Penny | 2010-03-29 |
+----+-------+------------+
5 rows in set (0.02 sec)

Answer 7

You could do a join on the date part of the date variable, which will rule out any where there is only one row for that date (because it has nothing to join to).

This example is in t-sql:

select  distinct l.*
from    @table l
join 
    @table r
on  convert(varchar, l.[date], 102) = convert(varchar, r.[date],102)
and l.id != r.id 

Answer 8

Here's the final version:

SELECT * 
FROM   table 
WHERE  convert(varchar, table.DataInput, 102) IN 
       (SELECT convert(varchar, table.DataInput, 102) 
        FROM table 
        GROUP BY convert(varchar, table.DataInput, 102) 
        HAVING COUNT(*) > 2)

Thank you all for your input! :)