freetiger freetiger - 8 months ago 17
SQL Question

how to get unique values in SQL?

I have a table like below

event_date id
---------- ---
2015-11-18 x1
2015-11-18 x2
2015-11-18 x3
2015-11-18 x4
2015-11-18 x5
2015-11-19 x1
2015-11-19 x2
2015-11-19 y1
2015-11-19 y2
2015-11-19 y3
2015-11-20 x1
2015-11-20 y1
2015-11-20 z1
2015-11-20 z2

Question: How to get unique count of id for every date (such that we get count of only those id which were not seen in the previous records)? Something like this:

event_date count(id)
----------- ---------
2015-11-18 5
2015-11-19 3
2015-11-20 2

Each ID should only be counted once regardless of whether it occurs within the same date group or otherwise.


Here is an answer that'll work although I am not sure I like it:

select t.event_date,
from ( 
      -- Record first occurrence of each id along with the earliest date occurred 
      select id,
              min(event_date) as event_date
       group by id
      ) t
group by t.event_date;

I know it works because I tested with your data to get the results you wanted.

This actually works for this data but if you had a date group that consisted only of duplicate ids, for example, if among rows, you had one more row ('2016-01-01', 'z2') this won't display any records for that 2016-01-01 because z2 is a duplicate. If you need to return a row within your results:

2016-01-01 0

then, you have to use a LEFT JOIN with the GROUP BY.

sqlfiddle here