Getting error of not a single group function in aggregated function-CodePudding

CREATE TABLE test1 (
    e_id       NUMBER(1),
    e_ques_id  NUMBER(10)
);

INSERT INTO test1 VALUES(1,3);
INSERT INTO test1 VALUES(1,4);

CREATE TABLE test_ref (
    code     NUMBER(1),
    c_value  VARCHAR2(20)
);

INSERT INTO test_ref VALUES(3,'May');
INSERT INTO test_ref VALUES(4,2022);

COMMIT;

Tool Used: SQL Developer(18c)

There are two tables test1 and test_ref and I want to fetch the value from the test_ref table in order to bring month and year column value.

Expected Output:

 ------ ------------ 
| e_id | month_year |
 ------ ------------ 
|    1 | May 2022   |
 ------ ------------

My attempt:

SELECT t.e_id,
LISTAGG(c_value,' ')month_year
FROM test1 t
JOIN test_ref tr ON(t.e_ques_id = tr.code)
WHERE t.e_ques_id IN(3,4)
GROUP BY t.e_id;

This is giving me the exact result that I am looking for but I need to add this logic in the existing view for which I need to use aggregated or case expression to bring the result. Hence I tried using CASE and MAX but it is giving me an error of not a single group function

My attempt (resulting in an error):

SELECT t.e_id,
MAX(CASE
WHEN t.e_ques_id IN(3,4) THEN LISTAGG(c_value,' ')
END )month_year
FROM test1 t
JOIN test_ref tr ON(t.e_ques_id = tr.code)
GROUP BY t.e_id;

CodePudding user response：

To me, it is kind of difficult to guess why existing view requires aggregate function or case expression.

Anyway: if you use max in its analytic form, skip case expression and - instead - move e_ques_id condition into the where clause (as you already did in query that works), you'd get this:

SQL>   SELECT t.e_id,
  2           MAX (LISTAGG (c_value, ' ') WITHIN GROUP (ORDER BY code))
  3              OVER (ORDER BY NULL) month_year
  4      FROM test1 t JOIN test_ref tr ON t.e_ques_id = tr.code
  5     WHERE t.e_ques_id IN (3, 4)
  6  GROUP BY t.e_id;

      E_ID MONTH_YEAR
---------- ---------------
         1 May 2022

SQL>

I have no idea whether this would (or would not) work in that view, but - you may try it.

CodePudding user response：

You can use conditional aggregation inside LISTAGG:

SELECT t.e_id,
       LISTAGG(CASE WHEN t.e_ques_id IN (3,4) THEN c_value END, ' ')
         WITHIN GROUP (ORDER BY t.e_ques_id) AS month_year
FROM   test1 t
       JOIN test_ref tr ON(t.e_ques_id = tr.code)
GROUP BY t.e_id;

Which, for the sample data:

CREATE TABLE test1 (e_id, e_ques_id) AS
SELECT 1, LEVEL FROM DUAL CONNECT BY LEVEL <= 5;

CREATE TABLE test_ref (code, c_value) AS
SELECT 1, 'Not this'        FROM DUAL UNION ALL
SELECT 3, 'May'             FROM DUAL UNION ALL
SELECT 4, '2022'            FROM DUAL UNION ALL
SELECT 5, 'Not this either' FROM DUAL;

Outputs:

E_ID MONTH_YEAR

1 May 2022

E_ID	MONTH_YEAR
1	May 2022

db<>fiddle here

CodePudding user response：

This is how you can use Max() OVER() though I don't understand what you want to do, but maybe this can help...

WITH 
    test1 AS
        (
            SELECT 1 "E_ID", 3 "E_QUES_ID" FROM DUAL UNION ALL
            SELECT 1 "E_ID", 4 "E_QUES_ID" FROM DUAL UNION ALL
            SELECT 1 "E_ID", 5 "E_QUES_ID" FROM DUAL UNION ALL
            SELECT 1 "E_ID", 6 "E_QUES_ID" FROM DUAL 
        ),
    test_ref AS
        (
            SELECT 3 "CODE", 'May' "C_VALUE" FROM DUAL UNION ALL
            SELECT 4 "CODE", '2022' "C_VALUE" FROM DUAL UNION ALL
            SELECT 5 "CODE", 'Jun' "C_VALUE" FROM DUAL UNION ALL
            SELECT 6 "CODE", '2022' "C_VALUE" FROM DUAL 
        )
SELECT 
    E_ID,
    Max(LISTAGG(MY, ' ') WITHIN GROUP (ORDER BY E_ID, E_QUES_ID)) OVER() "MONTH_YEAR"
FROM
    (
        SELECT 
            t.E_ID,
            t.E_QUES_ID,
            CASE WHEN t.E_QUES_ID IN(3, 4) THEN tr.C_VALUE END "MY"
        FROM
            test1 t
        INNER JOIN 
            test_ref tr ON(t.E_QUES_ID = tr.CODE)
        ORDER BY 
            t.E_ID,
            t.E_QUES_ID
    )
GROUP BY
    E_ID