From clause query
select count(*)
,sum(case when dy in ('sat','sun') then 1 end) weekends
from (
select to_char(trunc(sysdate,'yy')+rownum-1,'dy') dy
from dual
connect by level <= 365
)
COUNT(*) | WEEKENDS | 365 | 104 |
---|
With clause
with years as (
select to_char(trunc(sysdate,'yy')+rownum-1,'dy') dy
from dual
connect by level <= 365
)
select count(*)
,sum(case when dy in ('sat','sun') then 1 end) weekends
from years
Statement processed.
livesql.oracle.com bug
select * from ( -- needed for bug in livesql.oracle.com
with years as (
select to_char(trunc(sysdate,'yy')+rownum-1,'dy') dy
from dual
connect by level <= 365
)
select count(*)
,sum(case when dy in ('sat','sun') then 1 end) weekends
from years
)
COUNT(*) | WEEKENDS | 365 | 104 |
---|