CODEWITHSUNDEEP
sqlgroup-bycaseexasolution
Vega
Vega

Reputation: 2929

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

?

Upvotes: -1

Views: 275

Answers (3)

user5683823
user5683823

Reputation:

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.

Upvotes: 0

Popeye
Popeye

Reputation: 35900

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) ;

Upvotes: 0

VBoka
VBoka

Reputation: 9083

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

Upvotes: 1

Related Questions