Search code examples
sqlcountnetezza

Count grouped dates in SQL

I currently have data looking like: 

id         admin_date      grp
--------------------------------------------
1          3/10/2019        1
1          3/11/2019        1
1          3/23/2019        2
1          3/24/2019        2
1          3/25/2019        2
2          12/26/2017       1
2          2/27/2019        2
2          3/16/2019        3
2          3/17/2019        3

where grp is grouped consecutive dates. I want to count each of this grp, so with above data, I would like to get result of 5 (2 consecutive date groups for id 1, 3 consecutive date groups for id 2). Anyone have idea how to tackle this?

Solution

  • Do you just want count(distinct)? I don't think Netezza supports multiple arguments as in:

    select count(distinct id, grp)
from t;

    So a subquery can be used:

    select count(*)
from (select distinct id, grp from t) t;