I want to my database table date column to month, and wanted to calculate the data from month to month But, i getting this error again and again
SQL> desc fac_cus_chd
Name Null? Type
----------------------------------------- -------- ----------------------------
FDC NOT NULL VARCHAR2(9)
WHDATE NOT NULL DATE
SL_NO NOT NULL NUMBER(3)
ITEMCODE NOT NULL VARCHAR2(11)
DECCODE NOT NULL VARCHAR2(15)
GRADE NOT NULL VARCHAR2(1)
QTY NOT NULL NUMBER(9,2)
UNIT NOT NULL VARCHAR2(6)
CARTON VARCHAR2(5)
UPRICE NUMBER(9,2)
UPT VARCHAR2(1)
CARTON_DET VARCHAR2(5)
SQL> select SUM(QTY)
from fac_cus_chd
where whdate between to_date(to_char('01-JAN-2019', 'DD-MM-YYYY'), 'Month') AND to_date(to_char('31-DEC-2019', 'DD-MM-YYYY') ;
2 /
select to_date(to_char('WHDATE', 'DD-MM-YYYY'), 'Month') from fac_cus_chd
*
ERROR at line 1:
ORA-01722: invalid number
SQL>
I want o have value like
Month Qty
Jan 200000
Feb 33,0000
Mar 34,0000
APR 32293
May 242434
JUN 24242
JUL 24234
AUG 232423
SEP 242432
OCT 232342
NOV 33423
DEC 3233333
I think the solution would be something like that
with fac_cus_chd as(
select to_date('01-01-2019', 'DD-MM-YYYY') as whdate, 10 as qty from dual union all
select to_date('01-02-2019', 'DD-MM-YYYY') as whdate, 30 as qty from dual union all
select to_date('01-03-2019', 'DD-MM-YYYY') as whdate, 5 as qty from dual union all
select to_date('01-04-2019', 'DD-MM-YYYY') as whdate, 10 as qty from dual union all
select to_date('01-07-2019', 'DD-MM-YYYY') as whdate, 2 as qty from dual union all
select to_date('01-09-2019', 'DD-MM-YYYY') as whdate, 4 as qty from dual union all
select to_date('01-10-2019', 'DD-MM-YYYY') as whdate, 5 as qty from dual union all
select to_date('25-10-2019', 'DD-MM-YYYY') as whdate, 8 as qty from dual union all
select to_date('01-11-2019', 'DD-MM-YYYY') as whdate, 2 as qty from dual union all
select to_date('01-12-2019', 'DD-MM-YYYY') as whdate, 3 as qty from dual union all
select to_date('20-12-2019', 'DD-MM-YYYY') as whdate, 7 as qty from dual
)
select to_char(trunc(whdate, 'MON'), 'Mon') as month_name
, sum(qty) as quantity
from fac_cus_chd
where trunc(WHDATE, 'MON') between to_date('01-01-2019', 'DD-MM-YYYY')
and to_date('31-12-2019', 'DD-MM-YYYY')
group by trunc(whdate, 'MON')
order by trunc(whdate, 'MON')
you could then apply ordering etc
MONTH_NAME QUANTITY
Jan 10
Feb 30
Mar 5
Apr 10
Jul 2
Sep 4
Oct 13
Nov 2
Dec 10
9 rows