CODEWITHSUNDEEP
sqlpostgresqlcount
Jasmeet
Jasmeet

Reputation: 540

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.

Upvotes: 1

Views: 168

Answers (5)

Spade
Spade

Reputation: 2280

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

select t.event_date,
       count(1)
from ( 
      -- Record first occurrence of each id along with the earliest date occurred 
      select id,
              min(event_date) as event_date
       from
       mytable
       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

Upvotes: 4

Andy
Andy

Reputation: 56

Edit: Crap, sorry Spade. Below is ust an example of what Spade was referring to if you wanted a zero displayed for trivial event_date entries.

I would do something along the lines of this ... 

select 
a.event_date,
count(a.id) cnt_id
from
table_name a
left outer join 
(
    select x.id, min(x.event_date) min_event_date from table_name x
) b on 
    a.id = b.id AND 
    a.event_date = b.min_event_date
GROUP BY
a.event_date

Upvotes: 0

S.Bozzoni
S.Bozzoni

Reputation: 1002

SELECT   EVENT_DATE,COUNT (DISTINCT ID)
FROM     MYTABLE 
WHERE NOT EXISTS 
      (SELECT * FROM MYTABLE T2 WHERE     
       T2.EVENT_DATE<MYTABLE.EVENT_DATE AND T2.ID=MYTABLE.ID)
GROUP BY EVENT_DATE

Upvotes: 0

Daniel Nelson
Daniel Nelson

Reputation: 366

SELECT
    mytable.event_date
    event_date_count.id,
    event_date_count.event_date_count
FROM
    mytable
    INNER JOIN
        (
        SELECT
            id,
            event_date,
            COUNT(event_date) as event_date_count
        FROM
            mytable
        GROUP BY
            id,
           event_date
        ) event_date_count
    ON event_date_count.event_date = mytable.event_date

This would give you a resultset of the date, the id, and how many times that id was found in that date.

Unleash the power of joining on select statements with aggregates.

Upvotes: -1

Mureinik
Mureinik

Reputation: 312219

You could group by the date and apply a distinct count to the id per group:

SELECT   event_date, COUNT(DISTINCT id)
FROM     mytable
GROUP BY event_date

Upvotes: 4

Related Questions