Skip to content
Advertisement

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?

Advertisement

Answer

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;
User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement