CODEWITHSUNDEEP
sqlpostgresqlrecursive-query
Tito
Tito

Reputation: 671

recurcive CTE how to use it

I want to create a recursive cte by passing a date through it until it reaches final date. Can anyone show me an example of how to do it

I need to pass a starting date and a final date and make a count of how many students there were in every year!

where :date and :finaldate is an input

 with seventeen as 
(
    select  count(studentid)
    from students s
    join periodrange p on p.idx = s.periodrangeidx
    where p.validdate between :date and :finaldate
)

I want to have the sum of the students for each year

Would something like this even work or did i dream to far for recrusive CTE

Upvotes: 0

Views: 74

Answers (1)

Brad
Brad

Reputation: 3591

You dont need CTE, you can just use a group by and count:

select  count(studentid), extrac(year from p.validatdate) as Year
from students s
join periodrange p on p.idx = s.periodrangeidx
where p.validdate between :date and :finaldate
Group by extrac(year from p.validatdate), studentid

Upvotes: 2

Related Questions