I want to count the number of events by ID, restarting at 1 for every ID.
In pyhton I guess this would be a COUNT+1 but how to do it in SQL (Druid)?
Results I want:
Date Id Count_occurence_by_id
2022-02-02 13 1
2022-02-03 13 2
2022-02-04 13 3
2022-01-12 16 1
2022-02-09 16 2
2022-01-14 19 1
2022-03-12 19 2
2022-03-12 20 1
All help appreciated!
hi
hope this helps
drop table if exists #Data
create table #Data ( Dt date , ID int )
insert into #Data select '2022-02-02',13
insert into #Data select '2022-02-03',13
insert into #Data select '2022-02-04',13
insert into #Data select '2022-01-12',16
insert into #Data select '2022-02-09',16
insert into #Data select '2022-01-14',19
insert into #Data select '2022-03-12',19
insert into #Data select '2022-03-12',20
select * from #Data
SELECT
Dt
, ID
, ROW_NUMBER() OVER(partition by Id order by dt ) as Count_occurence_by_id
FROM
#Data
Thank you, this looks like it would work! However, I am using Druid and for some reason there is no ROW_NUMBER function... I am also trying in Looker SQL, but no luck. Is there an alternative function to ROW_NUMBER? I have tried to Google it with no useful results.
This is a Microsoft SQL Server site so most people will not know a lot about Druid. You will probably be better off posting on a Druid site. A quick search suggests there is a Druid extension for windowed functions like ROW_NUMBER():
hi
hope this helps
i used tally table
result is coming without using row number
declare @t table(n int)
insert into @t select 1 union all select 2 union all
select 3 union all select 4 union all select 5 union all
select 6 union all select 7 union all select 8 union all
select 9 union all select 10 union all select 11 union all
select 12 union all select 13 union all select 14 union all
select 15 union all select 16 union all select 17 union all
select 18 union all select 19 union all select 20
