Svein,

Thank you for your quick reply (and sorry for my late one). Yes, the
following works:

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

Actually, as I investigate all the issues, it seems I will be best
served by creating a mini-datawarehouse, since the long term usage
will involve several dimensions. Originally, I was hoping to do a
simple "table lookup" for each column of IDs but, in the end, that
means a join for each.

If you ever think of the way to accomplish my original Sql effort as
you thought might be possible, I'd be glad to see it.

Thanks again,

Pete

--- In firebird-support@yahoogroups.com, Svein Erling Tysvaer
<svein.erling.tysvaer@...> wrote:

>
> 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
>