Oracle supports `RETURNING` clause which could be very useful.

For example for data:

```
CREATE TABLE t(Id INT, Val varchar2(50));

INSERT INTO t(Id, Val)
SELECT 10,'a' FROM dual
UNION ALL SELECT 20,'b' FROM dual
UNION ALL SELECT 30,'a' FROM dual
UNION ALL SELECT 40,'b' FROM dual;

```

Query:

```
DECLARE
   l_cnt INT;
BEGIN
   DELETE FROM t RETURNING COUNT(*) INTO l_cnt;
   DBMS_OUTPUT.put_line('l_cnt: ' || l_cnt);
END;

```

> 
> l\_cnt: 4
> 
> 
> 

It supports MIN/MAX/AVG/SUM/LISTAGG:

```
DECLARE
   l_max INT;
   l_min INT;
   l_str VARCHAR2(100);
BEGIN
   DELETE FROM t 
   RETURNING MAX(id), MIN(id), LISTAGG(id, ',') WITHIN GROUP(ORDER BY id) 
   INTO l_max, l_min, l_str;
   DBMS_OUTPUT.put_line('l_max:'||l_max||' l_min:'||l_min||' l_str:'|| l_str);
END;

```

> 
> l\_max:40 l\_min:10 l\_str:10,20,30,40
> 
> 
> 

Unfortunately when combined with `DISTINCT` keyword I get an error:

```
DECLARE
   l_distinct_cnt INT;
BEGIN
   DELETE FROM t 
   RETURNING COUNT(DISTINCT val) INTO l_distinct_cnt ;
   DBMS_OUTPUT.put_line('l_distinct_cnt:' || l_distinct_cnt );
END;

```

> 
> **ORA-00934: group function is not allowed here**
> 
> 
> 

**[db<>fiddle demo](https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=bd46d55dbf0b2e264362d9aeb378130a)**

The question is why aggregate functions with `DISTINCT` are not allowed?
I am looking for an answer drawing from official sources.

---

**EDIT:**

Please note that `COUNT(DISTINCT ...)` was only an example. Same behavior is for `SUM(col)/SUM(DISTINCT col)` and any aggregate function that supports `DISTINCT` keyword.

**[SUM(val) vs SUM(DISTINCT val)](https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=cdcc8eedc47a5266a90d1a4958363aa7)**