CODEWITHSUNDEEP
sqloracle-database
Heisenberg
Heisenberg

Reputation: 5279

How to loop in Oracle SQL

Assume we have exampleDB and select contractdate like

SELECT DB.contractdate FROM exampleDB DB

contractdate 
2014/12/1
2015/12/1
2016/12/1
2017/12/1
2018/12/1
2019/12/1

I would like to count the policy number at each time like

each time   policy count
2014/1/1       0
2015/1/1       1
2016/1/1       2
2017/1/1       3
2018/1/1       4
2019/1/1       5

I tried

WHERE DB.contractdate <='2014/1/1';

But I must loop such code manually. How can I loop?

If the binning is every month,it is very stressful process.

can they be combined into one?

Best regards

Upvotes: 1

Views: 62

Answers (3)

Popeye
Popeye

Reputation: 35900

You can use COUNT analytical function with RANGE operator as follows:

SQL> with dataa(contractdate) as
  2  (
  3  select date '2014-12-01' from dual union all
  4  select date '2015-12-01' from dual union all
  5  select date '2016-12-01' from dual union all
  6  select date '2017-12-01' from dual union all
  7  select date '2018-12-01' from dual union all
  8  select date '2019-12-01' from dual
  9  )
 10  SELECT
 11      TRUNC(CONTRACTDATE, 'year') as "each time",
 12      COUNT(1) OVER(
 13          ORDER BY
 14              CONTRACTDATE DESC
 15          RANGE BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING
 16      ) as "policy count"
 17  FROM
 18      DATAA order by 1;

each time policy count
--------- ------------
01-JAN-14            0
01-JAN-15            1
01-JAN-16            2
01-JAN-17            3
01-JAN-18            4
01-JAN-19            5

6 rows selected.

SQL>

Cheers!!

Upvotes: 1

Ed Bangga
Ed Bangga

Reputation: 13006

you can use row_num() and trunc() to get 1st day of the month

SELECT TRUNC(DB.contractdate, 'MONTH'), row_number() over (order by DB.contractdate) -1 as policy_count
FROM exampleDB DB

Upvotes: 1

Arif Sher Khan
Arif Sher Khan

Reputation: 585

select contractdate as "each time",
       count(*) as "policy count" 
  from exampleDB 
where contractdate in (mention all dates you want)
group by contractdate

Hope this will help you.

Upvotes: 1

Related Questions