here i have two tables gen_ledger and transactions.gen_ledger have gl_code,gl_category in the transaction table we have entries corresponding to this gl_code
what i need is i need to calculate sum(cv_amount) from transactions for each month.Result should be like
gl_code gl_category month total
180302 A jan sum(cv_amount) for the month of jan
The problem is in the transaction table we only have dates.How can we get the desired out put.
CREATE TABLE GEN_LEDGER (
comp_code decimal(16, 0),
gl_code decimal(16, 0),
gl_category char(1));
INSERT INTO GEN_LEDGER VALUES (
1,
180302,
'A');
CREATE TABLE TRANSACTION (
comp_code decimal(16, 0),
branch_code decimal(16, 0),
cv_amount decimal(16, 3),
value_date datetime);
INSERT INTO TRANSACTION VALUES (
1,
2,
3280026.24,
TO_DATE('17-01-2017 00:00:00','DD-MM-YYYY HH24:MI:SS'));
INSERT INTO TRANSACTION VALUES (
1,
2,
102500.82,
TO_DATE('17-01-2017 00:00:00','DD-MM-YYYY HH24:MI:SS'));
INSERT INTO TRANSACTION VALUES (
1,
2,
284.99,
TO_DATE('20-10-2016 00:00:00','DD-MM-YYYY HH24:MI:SS'));
INSERT INTO TRANSACTION VALUES (
1,
2,
47595.44,
TO_DATE('20-10-2016 00:00:00','DD-MM-YYYY HH24:MI:SS'));
INSERT INTO TRANSACTION VALUES (
1,
2,
659.23,
TO_DATE('20-10-2016 00:00:00','DD-MM-YYYY HH24:MI:SS'));
INSERT INTO TRANSACTION VALUES (
1,
2,
23533.22,
TO_DATE('20-10-2016 00:00:00','DD-MM-YYYY HH24:MI:SS'));
INSERT INTO TRANSACTION VALUES (
1,
2,
2860.1,
TO_DATE('20-10-2016 00:00:00','DD-MM-YYYY HH24:MI:SS'));
INSERT INTO TRANSACTION VALUES (
1,
2,
4109.59,
TO_DATE('20-10-2016 00:00:00','DD-MM-YYYY HH24:MI:SS'));
INSERT INTO TRANSACTION VALUES (
1,
2,
18306.85,
TO_DATE('20-10-2016 00:00:00','DD-MM-YYYY HH24:MI:SS'));
INSERT INTO TRANSACTION VALUES (
1,
2,
88444.76,
TO_DATE('20-10-2016 00:00:00','DD-MM-YYYY HH24:MI:SS'));
INSERT INTO TRANSACTION VALUES (
1,
2,
25178.07,
TO_DATE('20-10-2016 00:00:00','DD-MM-YYYY HH24:MI:SS'));
INSERT INTO TRANSACTION VALUES (
1,
2,
6920.51,
TO_DATE('20-10-2016 00:00:00','DD-MM-YYYY HH24:MI:SS'));
INSERT INTO TRANSACTION VALUES (
1,
2,
19895.75,
TO_DATE('20-10-2016 00:00:00','DD-MM-YYYY HH24:MI:SS'));