PLSQL case when sum count ORA-00934: group function is not allowed here

腫魔沒人留言?快點下去搶頭香啊!

Reference:

  1. TechOnTheNet: Oracle / PLSQL: ORA-00934 Error Message
  2. STACK OVERFLOW: How to do countIf() in Oracle

Question: The PLSQL code below will cause ORA-00934: group function is not allowed here error message

select case
         when count(...) then
           ...
         else
           ...
       end
  from  some_table

But it will be no problem if in reverse order, e.g.

select sum(case
             when ... then
              1
             else
              0
           end)
  from some_table

You can count these situations you want by summing these numbers which being one while fitting the right condition.

Likely, you can also count how many kinds of situation by counting with differenct conditions.

select count(case
             when a>10 and b<5 then
               'a10b5'
             when a+b=5 then
               'ab5'
             else
              'others'
           end)
  from some_table
0 0 vote
Article Rating
訂閱(Subscribe)
來自於(Notify of)
0 Comments
Inline Feedbacks
View all comments