Hi Peter!

Your select below isn't quite the same as using a LEFT JOIN. It is identical if every order contains a proc_id that is in procs, but it is different if orders may have NULL in proc_id or if it can be a value that doesn't exist in procs. The LEFT JOIN is identical to your original subselect except where a proc_id is identical for two rows in procs (your original subselect would give an error about getting multiple values where expecting a singular result, whereas my LEFT JOIN would count each row in orders with such proc_id twice - just like your statement below).

I'm sorry to say that it is unlikely that I'll remember the way I thought was possible, the closest I can get to an answer is that I think it was written in this list less than one year ago (though time flies quickly). I know the reasoning behind the answer was sufficiently complex, so I believe it was written by Arno Brinkman or Dmitry Yemanov. But it might have been a similar situation to yours, without being identical.

Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Peter Welch
Sent: 2. mars 2008 18:34
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: Select statement column lists question - Invalid Token

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