CODEWITHSUNDEEP
sqloracle-database
Madmartigan
Madmartigan

Reputation: 453

count distinct group by day

I've this subquery results:

DATE        REGCODE  REGCODE2    REGCODE 3   REGCODE4    
02/01/15    70771      5            6           8
02/01/15    null       5            21          8  
02/01/15    70855      4            5           13  
02/01/15    null       ...         ...          ...
03/01/15    70771
03/01/15    70772
05/01/15    null
05/01/15    70855
05/01/15    70826

and i need to Count the different REGCODEs grouping by date, but if one of them is repeated in one of the PREVIOUS dates, this none don´t count anymore

so, i need something like that:

DATE          COUNT(REGCODE)
02/01/15           2
03/01/15           1
05/01/15           1

Thank u very much for your help.

Upvotes: 0

Views: 60

Answers (2)

SelvaS
SelvaS

Reputation: 2125

Try this. Its using ROW_NUMBER() and PARTITION BY with REGCODE.

declare @table table ([DATE] date, REGCODE int)

insert into @table
select '02/01/15', 70771 union
select '02/01/15', null union
select '02/01/15', 70855 union
select '02/01/15', null union
select '03/01/15', 70771 union
select '03/01/15', 70772 union
select '03/01/15', null union
select '05/01/15', 70855 union
select '05/01/15', 70826

select [DATE], COUNT([DATE]) from 
(
    select [DATE], REGCODE, 
    row_number() over (partition by  REGCODE order by [DATE]) rn 
    from @table 
) e 
where rn = 1 and regcode is not null
group by [DATE]

Upvotes: 0

symcbean
symcbean

Reputation: 48357

SELECT t.date, COUNT(DISTINCT t.regcode)
FROM yourtable t
WHERE NOT EXISTS (
   SELECT 1 
   FROM yourtable prev
   WHERE prev.regcode=t.regcode
   AND prev.date<t.date
)
GROUP BY t.date;

(may need an index on regcode, date)

Upvotes: 3

Related Questions