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