I have a table like this in oracle

i need to find sum of amount in each month/year. The result need to be like this:

i haave selected only the data for 1 client and wrote code like this, but sql throws an error: npt group by expression

select Date_a, cl_id , sum(amount)
from table1
GROUP BY EXTRACT(year from Date_a), EXTRACT(month from Date_a)
where cl_id  = '010'

CodePudding user response：

It is analytic form of the SUM function you need:

SQL> with test (date_a, cl_id, amount) as
  2    (select date '2020-01-06', 10, 10    from dual union all
  3     select date '2020-01-09', 10, 12.1  from dual union all
  4     select date '2020-01-09', 10, 5     from dual union all
  5     select date '2020-01-13', 10, 8     from dual union all
  6     select date '2020-02-09', 10, 13.1  from dual union all
  7     select date '2020-02-12', 10, 0     from dual
  8    )
  9  select date_a, cl_id,
 10    sum(amount) over (partition by to_char(date_a, 'yyyymm')) sum_a
 11  from test  ;

DATE_A          CL_ID      SUM_A
---------- ---------- ----------
06.01.2020         10       35,1
09.01.2020         10       35,1
09.01.2020         10       35,1
13.01.2020         10       35,1
09.02.2020         10       13,1
12.02.2020         10       13,1

6 rows selected.

SQL>

CodePudding user response：

You want all the rows in the output so you want to use an analytic aggregation function rather than using GROUP BY:

SELECT Date_a,
       cl_id,
       sum(amount) OVER (PARTITION BY TRUNC(date_a, 'MM')) AS sum_a
FROM   table1
where  cl_id = '010';

Which, for your sample data:

CREATE TABLE table1 (Date_a, cl_id, amount) AS
SELECT DATE '2020-01-06', '010', 10.0 FROM DUAL UNION ALL
SELECT DATE '2020-01-09', '010', 12.1 FROM DUAL UNION ALL
SELECT DATE '2020-01-09', '010',  5.0 FROM DUAL UNION ALL
SELECT DATE '2020-01-13', '010',  8.0 FROM DUAL UNION ALL
SELECT DATE '2020-02-09', '010', 13.1 FROM DUAL UNION ALL
SELECT DATE '2020-02-12', '010',  0.0 FROM DUAL UNION ALL
SELECT DATE '2020-02-01', '010', 14.0 FROM DUAL;

Outputs:

DATE_A	CL_ID	SUM_A
06-JAN-20	010	35.1
09-JAN-20	010	35.1
09-JAN-20	010	35.1
13-JAN-20	010	35.1
09-FEB-20	010	27.1
12-FEB-20	010	27.1
01-FEB-20	010	27.1

db<>fiddle here