CODEWITHSUNDEEP
mysql
SlackerZeitgeist
SlackerZeitgeist

Reputation: 29

Only Count First Instance when Using Both DISTINCT and GROUP BY

Say I have the following table,

    timedate    id
    2015-01-01   1
    2015-01-01   2
    2015-01-01   3
    2015-01-01   4
    2015-01-02   1
    2015-01-02   2
    2015-01-02   5
    2015-01-02   6
    2015-01-03   2
    2015-01-03   3

This query

 SELECT 
       COUNT(DISTINCT `id`) as total,
        timedate
 FROM Table1
 GROUP BY timedate

produces the following results

total    timedate
  4      2015-01-01
  4      2015-01-02
  2      2015-01-03

Because it's counting the distinct id within each group.

How do I can I get the count of each distinct id without counting it in subsequent groups? e.g. a result like this:

total    timedate
  4      2015-01-01
  2      2015-01-02
  0      2015-01-03

Upvotes: 2

Views: 47

Answers (1)

JNevill
JNevill

Reputation: 50034

You're best off using two subqueries here. One to get the id and it's min(timedate) and another to get distinct timedates:

SELECT 
       COUNT(DISTINCT `id`) as total,
        t1.timedate
 FROM 
     (SELECT DISTINCT TimeDate FROM Table1) as t1
     LEFT OUTER JOIN (SELECT min(Timedate) as firsttimedate, id FROM Table1 GROUP BY id) as t2 ON
         t1.timedate = t2.firsttimedate
 GROUP BY t1.timedate

Upvotes: 1

Related Questions