How can you group by the result of a case if case is not supported in the group by clause in intebase

Question

I have tried using the columns alias, column number, using the case statement in the group by with no luck. Is this a shortcoming of interbase?

select case when vp.preferredvendor = 'Y' then vp.name else 'Misc' end as vendor, sum(sa.totalfare) from SalesActivity(1,1,2, '2014-01-01', '2014-02-01') sa join booking bk on bk.bookingno = sa.bookingno join profile vp on bk.vendor_linkno = vp.profileno group by vendor

Answer 1

Repeat the case:

select (case when vp.preferredvendor = 'Y' then vp.name
             else 'Misc'
        end) as vendor,
       sum(sa.totalfare)
from SalesActivity(1,1,2, '2014-01-01', '2014-02-01') sa join
     booking bk
     on bk.bookingno = sa.bookingno join
     profile vp
     on bk.vendor_linkno = vp.profileno
group by (case when vp.preferredvendor = 'Y' then vp.name
               else 'Misc'
          end);

As a side note, some databases do allow the use of column aliases in the group by clause, but definitely not all of them and it is definitely not required by the standard.

Answer 2

I'm not familiar with Interbase, but given what you've tried unsuccessfully, the next step would be to try a sub-query:

select vendor, sum(totalfare)
from (
   select
     case
       when vp.preferredvendor = 'Y' then vp.name
       else 'Misc'
     end as vendor,
     sa.totalfare
   from
     SalesActivity(1,1,2, '2014-01-01', '2014-02-01') sa
   join
     booking bk on bk.bookingno = sa.bookingno
   join
     profile vp on bk.vendor_linkno = vp.profileno)
group by vendor