How to loop in Oracle SQL

Question

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

Why do you think a query statement loop is necessary?

Ross Bush
– Ross Bush

2019-09-30 03:06:28 +00:00
Commented Sep 30, 2019 at 3:06 — Ross Bush
– Ross Bush, Commented Sep 30, 2019 at 3:06

Arif Sher Khan · Accepted Answer · 2019-09-30 03:09:22Z

1

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.

answered Sep 30, 2019 at 3:09

Arif Sher Khan

5995 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ed Bangga · Accepted Answer · 2019-09-30 03:12:04Z

1

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

edited Sep 30, 2019 at 3:12

answered Sep 30, 2019 at 3:06

Ed Bangga

13k4 gold badges18 silver badges31 bronze badges

Comments

Popeye · Accepted Answer · 2019-09-30 05:03:53Z

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!!

Collectives™ on Stack Overflow

How to loop in Oracle SQL

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related