sqlgroup-bycaseexasolution

GROUP BY with CASE WHEN?


I have table k with:

num2 | count
aa1  | 10
aa2  | 5
bb   | 2
bb   | 4
cc   | 80

And table t with:

num1 | num2
a    | aa1
a    | aa2
" "  | bb
" "  | bb
" "  | cc

Now I would like to get MIN and MAX count for every num1 while replacing " " in num1 with num2(Fill rows in column A with value of column B if condition in column A is met):

Replacing " " in num1 with num2 (works):

SELECT (CASE
WHEN t.num1 = ' ' THEN TO_CHAR(k.num2)
ELSE t.num1
END) AS 'num1',
k.num2 AS 'num2',
k.count AS 'count'
--
FROM table1 AS k
--
LEFT JOIN table2 AS t
ON k.num2 = t.num2
--
WHERE k.count > 1
--
;

Resulting in:

num1 | num2 | count
a    | aa1  | 10
a    | aa2  | 5
bb   | bb   | 2
bb   | bb   | 4
cc   | cc   | 80

But getting MIN + MAX of every num1 with a GROUP BY does not:

SELECT (CASE
WHEN t.num1 = ' ' THEN TO_CHAR(k.num2)
ELSE t.num1
END) AS 'num1',
MIN(k.count) AS 'count_MIN',
MAX(k.count) AS 'count_MAX'
--
FROM table1 AS k
--
LEFT JOIN table2 AS t
ON k.num2 = t.num2
--
WHERE k.count > 1
--
GROUP BY (CASE
WHEN t.num1 = ' ' THEN TO_CHAR(k.num2
ELSE t.num1
END)
--
;

Which should result in:

num1 | count_MIN | count_MAX
a    | 5         | 10
bb   | 2         | 4
cc   | 80        | 80

But when I run the code above I get the error in DBeaver:

SQL-Error [4200]: not a valid GROUP BY expression

?


Solution

  • Your 2nd query does not throw error: "SQL-Error [4200]: not a valid GROUP BY expression". It is resulting in : "ORA-00907: missing right parenthesis".

    SELECT CASE WHEN t.num1 = ' ' THEN 
                     TO_CHAR(k.num2)
           ELSE 
                     t.num1
           END num1
           , MIN(k.count)  count_MIN
           , MAX(k.count)  count_MAX
    FROM table1 k
    LEFT JOIN table2 t ON k.num2 = t.num2
    WHERE k.count > 1
    GROUP BY CASE WHEN t.num1 = ' ' THEN 
                       TO_CHAR(k.num2)
             ELSE 
                       t.num1
             END;
    

    Here is a demo:

    DEMO

    Few more things:

    1. TO_CHAR function - you need to close the bracket after the value
    2. You need to put case when else end inside of the brackets
    3. You do not need to put column aliases inside of the single quotes
    4. COUNT - do not use it as a name of the column
    5. Do not use AS keyword when naming table alias