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
?
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:
Few more things: