Hi, Peter!

With Firebird 2.1 (I don't know about Firebird 2.0), you can do

select count(*), cast(dt.aDT as date), dt.category
from
(select o.*
,(select p.category from procedures p where proc_id = orders.proc_id )
as category
from orders o) dt
group by 2, 3
order by 1;

All versions of Firebird should be able to handle something like this
using a VIEW, and - of course - the obvious LEFT JOIN that I simply
forgot and that should work almost regardless of which version you are
using (this would be my preferred way to do what you're trying to do):

select count(*)
, cast(o.aDT as date)
, p.category
from orders o
left join procedures p on p.proc_id = o.proc_id
group by 2, 3
order by 1;

I think your original select may work as well if you do something
clever. At least I think I had a slightly similar situation a while ago
that got solved on this list using an additional field in the group by
(I remember it surprised me when I saw the solution, but I don't quite
remember what was my problem, nor what the solution was).

HTH,
Set

Peter Welch wrote:

> I want to do a fairly simple 'group by' select statement but only
> certain combinations work.
>
> What I want is the following to work:
>
> select count(*)
> , cast(aDT as date)
> ,(select category from procedures where proc_id = orders.proc_id )
> from orders
> group by 1,2
> order by 1;
>
> Running the above causes the ambiguous Invalid Token error (with or
> without the count(*) column).
>
> I can do the following:
>
> select
> cast(aDT as date)
> ,(select category from procedures where proc_id = orders.proc_id )
> from orders
> order by 1;
>
> but no 'group by' or count(*)
>
> Or, I can do the following:
>
> select count(*)
> , cast(aDT as date)
> from orders
> group by 2
> order by 1;
>
> But no '(select category from ....'
>
> I would prefer not to turn this into a stored procedure, but don't
> see any other way.
>
> Any suggestions greatly appreciated,
>
> Pete