We have two queries Query1 and Query2.I understood query2 output.if i remove group by deptno for query2 it is showing error not a single group function.I believe group by is mandatory for LISTAGG function.In query 1 we are not using group by clause in a with clause.How query1 is running without group by clause and how it will do concatenation without group by clause?
Query1:
WITH t AS
(SELECT 1 c1,'x' c2 FROM dual
UNION
SELECT 2 c1,'y' c2 FROM dual
UNION
SELECT 3 c1,'z' c2 FROM dual
) ,
a AS
(SELECT LENGTH(LISTAGG(c2, '') WITHIN GROUP (
ORDER BY c2)) ln,
LISTAGG(c2, '') WITHIN GROUP (
ORDER BY c2) cont
FROM t
)
SELECT * from a
---
Query2:
select deptno,LISTAGG(sal, ',') WITHIN GROUP (
ORDER BY sal) sal from emp
group by deptno
DEPTNO , SAL
| 10 | 1300,2450,5000 |
| 20 | 800,1100,2975,3000,3000 |
| 30 | 950,1250,1250,1500,1600,2850 |