CODEWITHSUNDEEP
sqlsql-server
Adnan Niloy
Adnan Niloy

Reputation: 469

Count records which appeared in each previous years

Say I have a table which has columns id and year. I want to find out the number of ids which appeared in every previous years.

Sample Table

id | year | 
---+------+
 1 | 2017 |
 2 | 2017 | 
 1 | 2018 | 
 2 | 2018 |
 3 | 2018 |
 4 | 2019 |
 3 | 2019 |
 1 | 2019 |

Expected Output

 | 2017 | 2 |
 | 2018 | 2 |
 | 2019 | 1 |

In 2017, 1 and 2 appeared so count is 2. In 2018, 1 2 and 3 appeared but only 1 and 2 appeared in 2017 so count for 2018 is 2. In 2019, 1 3 and 4 appeared, but only 1 appeared in 2017 and 2018 so the count for 2019 is 1.

Upvotes: 0

Views: 83

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

You can enumerate the ids and the year and compare them:

select year, count(*)
from (
    select t.*,
        row_number() over (partition by id order by year) as seqnum_id,
        dense_rank() over (order by year) as seqnum_year
    from t
) t
where seqnum_id = seqnum_year
group by year;

Here is a db<>fiddle.

Upvotes: 1

Related Questions