I can't make this query work with SUM function

This query gives an error:

select ep, 
  case
    when ob is null and b2b_ob is null then 'a'
    when ob is not null or b2b_ob is not null then 'b'
    else null
  end as type,
  sum(b2b_d + b2b_t - b2b_i) as sales
from table
where ...
group by ep, type

Error: ORA-00904: "TYPE": invalid identifier

When I run it with group by ep, the error message becomes:

ORA-00979: not a GROUP BY express开发者_如何转开发ion

The whole query works OK if I remove the lines sum(b2b_d+b2b_t-b2b_i) as sales and group by ..., so the problem should be related to SUM and GROUP BY functions. How can I make this work? Thanks in advance for your help.

Unfortunately SQL doesn't allow you to use the column aliases in the GROUP BY clause, so you either have to repeat the entire CASE there like this:

select ep, 
  case
    when ob is null and b2b_ob is null then 'a'
    when ob is not null or b2b_ob is not null then 'b'
    else null
  end as type,
  sum(b2b_d + b2b_t - b2b_i) as sales
from table
where ...
group by ep,
  case
    when ob is null and b2b_ob is null then 'a'
    when ob is not null or b2b_ob is not null then 'b'
    else null
  end

or use an in-line view like this:

select ep, 
  type,
  sum(b2b_d + b2b_t - b2b_i) as sales
from
( select ep, 
    case
      when ob is null and b2b_ob is null then 'a'
      when ob is not null or b2b_ob is not null then 'b'
      else null
    end as type,
    b2b_d,
    b2b_t,
    b2b_i
  from table
  where ...
)
group by ep, type