CODEWITHSUNDEEP
sql
Alex C
Alex C

Reputation: 45

SQL - Count idUser with different creationDate

I have this table

ID / creationDate
2  / 2012-06-28 04:58:27
2  / 2012-07-10 14:26:51
2  / 2013-04-18 00:35:36
3  / 2012-07-02 16:47:00
4  / 2012-07-02 20:24:22
5  / 2012-07-03 00:32:17
6  / 2012-07-05 04:40:02
6  / 2012-07-06 18:17:08
6  / 2013-04-28 22:28:54

I would like to count the number of ID which have 2 or more different creationDate. In my example, I would like to have 2 (ID 2 and 6 have different creationDate).

I tried

SELECT COUNT(ID) FROM table
GROUP BY ID
HAVING count(DISTINCT creationDate)>1

Upvotes: 3

Views: 100

Answers (2)

Hart CO
Hart CO

Reputation: 34774

SELECT COUNT(*)
FROM (SELECT ID,COUNT(DISTINCT creationDate)'CT'
      FROM Table
      GROUP BY ID
      HAVING COUNT(DISTINCT creationDate) > 1
     )sub

If using an RDBMS that supports SUM() OVER() you could use:

SELECT TOP 1 SUM(COUNT(DISTINCT ID))OVER()
FROM Table1
GROUP BY ID
HAVING COUNT(DISTINCT creationDate) > 1

Demo: SQL Fiddle

Upvotes: 2

Luis LL
Luis LL

Reputation: 2993

try this:

SELECT COUNT(ID)
FROM (
    SELECT COUNT(ID) AS IDCnt, ID
    FROM table
    GROUP BY ID
    HAVING count(ID)>1
    ) a

Upvotes: 1

Related Questions