GROUP BY with CASE WHEN?

Question

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 data has one table but your query has two, which makes your question quite unclear. — GMB, Dec 03 '20 at 15:21
Hi @Vegam in two places you are missing right bracket (CASE WHEN and GROUP BY). Also you still have not seen my answer because you still have not removed AS keyword when giving table aliases ? Check the answer below... — VBoka, Dec 03 '20 at 15:48

VBoka · Accepted Answer · 2020-12-03T15:42:00.880

1

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:

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

edited Dec 03 '20 at 15:42

answered Dec 03 '20 at 15:34

VBoka

8,995
3
16
24

Thanks for the answer. To 3: without quotes Oracle returns everything in uppercase, I want "num1" instead of "NUM1". To 4: column is named differently than "count" but will it in mind. To 5: Why not? – Vega Dec 03 '20 at 16:35
Hi @Vega For number 3. Ok, then you need to use double quotes and not single quotes. So "num1" and not 'num1'. For number 4. OK. Because Oracle does not support the use of AS keyword in FROM section. You can use the AS keyword when naming aliases for columns like `SELECT MIN(k.count) AS count_MIN` but you can not use it when naming aliases for tables like `FROM table1 AS k` you need to do it without AS keyword. I found one link: https://www.techonthenet.com/oracle/alias.php – VBoka Dec 03 '20 at 16:56
3: 'num1' works, "num1" too. 4: FROM xxx AS 'yyy' works as well, I can then use yyy.columnname in SELECT. Checked my DB, it's Exasol, not Oracle. My bad. – Vega Dec 03 '20 at 17:24
Oh, then I understand, I was commenting Oracle :) – VBoka Dec 03 '20 at 17:31

score 0 · Answer 2 · answered Dec 03 '20 at 15:44

0

You can use the coalesce and TRIM function as follows:

SELECT COALESCE(TRIM(t.num1), t.num2) AS 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 COALESCE(TRIM(t.num1), t.num2) ;

answered Dec 03 '20 at 15:44

Popeye

35,427
4
10
31

The OP, presumably, distinguishes between a single space and a string of multiple spaces in column T.NUM1. Only a single space should be replaced with NUM2. Moreover, presumably the string 'cc' and the string 'cc ' are distinct in the OP's problem; your application of TRIM will collapse them onto a single value. – Dec 03 '20 at 15:52
I can understand @mathguy . but I considered it very practically – Popeye Dec 03 '20 at 16:41
I assume by that you mean "the OP will not have strings of multiple spaces, or strings like 'cc ', in his data, so we can ignore that possibility". If that is what you meant, that's very risky. That's one of the main ways we end up with big, unexpected bugs that are almost impossible to diagnose. – Dec 03 '20 at 16:46
Yeap. You are right @mathguy . I have just seen the sample data and expected output. – Popeye Dec 03 '20 at 16:47

score 0 · Answer 3 · answered Dec 03 '20 at 15:50

0

I used a simplified expression to get the modified NUM1 (replacing single space with NUM2). You must use exactly the same expression in SELECT and in GROUP BY.

select nvl(nullif(t.num1, ' '), t.num2) as num1
     , min(count_)                      as min_count
     , max(count_)                      as max_count
from   t left outer join k on t.num2 = k.num2
group  by nvl(nullif(t.num1, ' '), t.num2)
order  by num1
;

NUM1  MIN_COUNT  MAX_COUNT
---- ---------- ----------
a             5         10
bb            2          4
cc           80         80

Note that I used the column name COUNT_ (with a trailing underscore) in my tests; COUNT is a reserved keyword, it can't be a column name.

answered Dec 03 '20 at 15:50

You must use exactly the same expression in SELECT and in GROUP BY - That is not the problem OP has . – VBoka Dec 03 '20 at 15:51
@VBoka - I know; I was just reminging them of that important fact. I did not say or imply that was the problem in his code. I wrote the query from scratch, I didn't try to fix his code (as you can probably tell by comparing them). – Dec 03 '20 at 15:53
Ok, I just understood it like you are telling the OP that he did not use the same expression in SELECT and GROUP BY. Form my point of view it was aimed like that... – VBoka Dec 03 '20 at 15:55
@VBoka - my point was, if I use a different expression for the modified NUM1, I must remember to change it in both places - not just in SELECT. A reminder to the OP, to myself, and to anyone else who looks at his code and then at mine. – Dec 03 '20 at 16:25

GROUP BY with CASE WHEN?

3 Answers3