teratail
回答率
85.30%
teratail header banner
teratail header banner
質問するログイン新規登録
トップOracle Databaseに関する質問Oracleのテーブルから月ごとのレコード数を求めたい

編集履歴

回答編集履歴

3

追記

2019/04/04 07:34

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -5,6 +5,7 @@
5
5
  group by to_char(CREATED_DTM, 'YYYYMM')
6
6
  ```
7
7
  基準の月より６か月分なら、こんな感じ。※sysdateを使用して現時点より６か月分
8
+ [ADD_MONTHS](https://www.shift-the-oracle.com/sql/functions/add_months.html)
8
9
  ```SQL
9
10
  SELECT
10
11
  SUM(case when to_char(CREATED_DTM, 'YYYYMM')=to_char(ADD_MONTHS(SYSDATE, -5), 'YYYYMM') then 1 else 0 end) as SUMMARY1

2

推敲

2019/04/04 07:34

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -7,12 +7,12 @@
7
7
  基準の月より６か月分なら、こんな感じ。※sysdateを使用して現時点より６か月分
8
8
  ```SQL
9
9
  SELECT
10
- SUM(case when to_char(CREATED_DTM, 'YYYYMM')= to_char(ADD_MONTHS(SYSDATE, -5), 'YYYYMM') then 1 else 0 end) as SUMMARY1
10
+ SUM(case when to_char(CREATED_DTM, 'YYYYMM')=to_char(ADD_MONTHS(SYSDATE, -5), 'YYYYMM') then 1 else 0 end) as SUMMARY1
11
- , SUM(case when to_char(CREATED_DTM, 'YYYYMM')= to_char(ADD_MONTHS(SYSDATE, -4), 'YYYYMM') then 1 else 0 end) as SUMMARY2
11
+ , SUM(case when to_char(CREATED_DTM, 'YYYYMM')=to_char(ADD_MONTHS(SYSDATE, -4), 'YYYYMM') then 1 else 0 end) as SUMMARY2
12
- , SUM(case when to_char(CREATED_DTM, 'YYYYMM')= to_char(ADD_MONTHS(SYSDATE, -3), 'YYYYMM') then 1 else 0 end) as SUMMARY3
12
+ , SUM(case when to_char(CREATED_DTM, 'YYYYMM')=to_char(ADD_MONTHS(SYSDATE, -3), 'YYYYMM') then 1 else 0 end) as SUMMARY3
13
- , SUM(case when to_char(CREATED_DTM, 'YYYYMM')= to_char(ADD_MONTHS(SYSDATE, -2), 'YYYYMM') then 1 else 0 end) as SUMMARY4
13
+ , SUM(case when to_char(CREATED_DTM, 'YYYYMM')=to_char(ADD_MONTHS(SYSDATE, -2), 'YYYYMM') then 1 else 0 end) as SUMMARY4
14
- , SUM(case when to_char(CREATED_DTM, 'YYYYMM')= to_char(ADD_MONTHS(SYSDATE, -1), 'YYYYMM') then 1 else 0 end) as SUMMARY5
14
+ , SUM(case when to_char(CREATED_DTM, 'YYYYMM')=to_char(ADD_MONTHS(SYSDATE, -1), 'YYYYMM') then 1 else 0 end) as SUMMARY5
15
- , SUM(case when to_char(CREATED_DTM, 'YYYYMM')= to_char(SYSDATE, 'YYYYMM') then 1 else 0 end) as SUMMARY6
15
+ , SUM(case when to_char(CREATED_DTM, 'YYYYMM')=to_char(SYSDATE, 'YYYYMM') then 1 else 0 end) as SUMMARY6
16
16
  from テーブル
17
17
  where to_char(CREATED_DTM, 'YYYYMM') >= to_char(ADD_MONTHS(SYSDATE, -5), 'YYYYMM')
18
18
  ```

1

追記

2019/04/04 07:32

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -3,4 +3,16 @@
3
3
  select to_char(CREATED_DTM, 'YYYYMM') as CREATED_MONTH, count(*) as cnt
4
4
  from テーブル名
5
5
  group by to_char(CREATED_DTM, 'YYYYMM')
6
+ ```
7
+ 基準の月より６か月分なら、こんな感じ。※sysdateを使用して現時点より６か月分
8
+ ```SQL
9
+ SELECT
10
+ SUM(case when to_char(CREATED_DTM, 'YYYYMM')= to_char(ADD_MONTHS(SYSDATE, -5), 'YYYYMM') then 1 else 0 end) as SUMMARY1
11
+ , SUM(case when to_char(CREATED_DTM, 'YYYYMM')= to_char(ADD_MONTHS(SYSDATE, -4), 'YYYYMM') then 1 else 0 end) as SUMMARY2
12
+ , SUM(case when to_char(CREATED_DTM, 'YYYYMM')= to_char(ADD_MONTHS(SYSDATE, -3), 'YYYYMM') then 1 else 0 end) as SUMMARY3
13
+ , SUM(case when to_char(CREATED_DTM, 'YYYYMM')= to_char(ADD_MONTHS(SYSDATE, -2), 'YYYYMM') then 1 else 0 end) as SUMMARY4
14
+ , SUM(case when to_char(CREATED_DTM, 'YYYYMM')= to_char(ADD_MONTHS(SYSDATE, -1), 'YYYYMM') then 1 else 0 end) as SUMMARY5
15
+ , SUM(case when to_char(CREATED_DTM, 'YYYYMM')= to_char(SYSDATE, 'YYYYMM') then 1 else 0 end) as SUMMARY6
16
+ from テーブル
17
+ where to_char(CREATED_DTM, 'YYYYMM') >= to_char(ADD_MONTHS(SYSDATE, -5), 'YYYYMM')
6
18
  ```