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

It would be helpful to show us the all of the queries that you tried (you mention three, but only show one), along with the actual error message you receive for each of them. — Allan, Nov 28 '14 at 15:28

score 1 · Answer 1 · answered Nov 28 '14 at 15:24

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.

answered Nov 28 '14 at 15:24

Gordon Linoff

1,242,037
58
646
786

It returns an sql error saying there is an invalid token right after the group by. Sory interbase does not support the case in the group by portion of the statement. – wizhippo Jan 12 '15 at 14:10
@wizhippo . . . Are you allowed to do `group by vendor` or use subqueries? – Gordon Linoff Jan 12 '15 at 19:40
dosen't support `group by vendor` or subqueries. Best I can do is use a view and then group by using the view. Seems like a bad limitation of interbase. – wizhippo Jan 13 '15 at 21:34

score 0 · Answer 2 · answered Nov 28 '14 at 15:26

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

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

2 Answers2