Hi,
I am trying to group the records in a table by year. I don't know why it isn't working.
When I try it with a sample data set, it works fine.
for example, this query has 2 dates in 2018 and 1 date in 2019. The group by works as expected.
SQL> set echo on;
SQL> with data_set(date_value) as
2 (
3 select to_date('9/26/2018','mm/dd/yyyy') from dual union all
4 select to_date('9/27/2018','mm/dd/yyyy') from dual union all
5 select to_date('9/28/2019','mm/dd/yyyy') from dual
6 )
7 select to_char(date_value,'YYYY') year_value, count(*) from data_set group by to_char(date_value,'YYYY');
YEAR COUNT(*)
---- ----------
2019 1
2018 2
When I try it on one of my tables, it doesn't work. It list all the dates separately instead of grouping them. I have used the ddl function in Sql Developer to show the table structure.
SQL> ddl ref_clearing_calendar;
CREATE TABLE "ICEREF"."REF_CLEARING_CALENDAR"
( "CALENDAR_NAME" CHAR(17),
"BUSINESS_DATE" DATE,
"UPDATE_TS" TIMESTAMP (6) DEFAULT systimestamp,
CONSTRAINT "PK_REF_CLEARING_CALENDAR" PRIMARY KEY ("BUSINESS_DATE")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "REF_DATA" ENABLE,
SUPPLEMENTAL LOG GROUP "GGS_92500" ("BUSINESS_DATE") ALWAYS,
SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS,
SUPPLEMENTAL LOG DATA (UNIQUE INDEX) COLUMNS,
SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "REF_DATA" ;
SQL>
SQL> SELECT
2 to_char(business_date, 'yyyy'),
3 COUNT(*)
4 FROM
5 ref_clearing_calendar
6 GROUP BY
7 to_char(business_date, 'yyyy');
TO_C COUNT(*)
---- ----------
2000 1
2000 1
2000 1
2000 1
2000 1
2000 1
2000 1
2000 1
2000 1
2000 1
2000 1
TO_C COUNT(*)
---- ----------
2000 1
2000 1
2000 1
2000 1
2000 1
2000 1
2000 1
2000 1
2000 1
2000 1
2000 1
TO_C COUNT(*)
---- ----------
2000 1
2000 1
2000 1
2000 1
2000 1
2000 1
2000 1
2000 1
2000 1
2000 1
I tried to create a sample table with date values but I wasn't able to replicate the issue on a different object. What am I doing wrong?
Version: 12.2.0.1.0
Thanks!