I have two tables table_a and table_b as below. column invdate is of date datatype and column amount is of number datatype.
table_a
| invdate | amount |
|---|---|
| 20-01-2021 | 50 |
| 20-01-2021 | 100 |
| 20-02-2021 | 50 |
| 20-03-2021 | 50 |
table_b
| invdate | amount |
|---|---|
| 01-01-2021 | 250 |
| 01-02-2021 | 300 |
| 01-03-2021 | 40 |
| 01-03-2021 | 50 |
I am doing a UNION ALL of both tables to get the sum of the amounts based on only month and year.
SELECT to_char(invdate, 'MM-YYYY') as "Date", sum(amount) as "Total" FROM ( SELECT to_char(invdate, 'MM-YYYY'), amount FROM table_a UNION ALL SELECT to_char(invdate, 'MM-YYYY'), amount FROM table_b ) GROUP BY to_char(invdate, 'MM-YYYY') ORDER BY to_char(invdate, 'MM-YYYY') asc; to get the final output like below
| Date | Total |
|---|---|
| 01-2021 | 400 |
| 02-2021 | 350 |
| 03-2021 | 140 |
but it gives me the below error.
ORA-00904: "INVDATE": invalid identifier 00904. 00000 - "%s: invalid identifier" *Cause: *Action: Error at Line: 10 Column: 18 What am I doing wrong here?
