At 02:46 AM 26/05/2003 -0700, you wrote:

>Is it not possible to do a sum() on a subselect?

No. A subquery must return a scalar result (one single value). You can't
perform an aggregate operation on a scalar result.

>I have the following query:
>
>select proddept,
> count(*) as thecount,sum(prodinventory) as theinv,
> sum(prodprice*prodinventory) as price,
> sum(vpcost*prodinventory) as cost,
> sum(select sum(fifocost*fifoqty) from fifocosts where
>fifobarcode=A.prodbarcode) as paidcost
>from products A left outer join vendorproducts on vpupc=prodbarcode and
>vpvendor=prodsource
>group by proddept
>order by proddept
>
>It's saying "Token Unknown" on the "select" in the 5th line.
>
>I'm basically trying to do a double grouping (two detail layers deep).

Look at that subclause as you have it now - does sum(select sum(..)) make
any logical sense? What do you envisage as the second layer? How could
your grouping be "two detail layers deep" when you have only one grouping
column?

Guessing at what you are trying to achieve, i.e. a sum of all sums, I'd
suggest making a denormalised view of the inner nesting and then performing
a DSQL aggregation on that view at runtime...or put it into a selectable
stored procedure with two nested FOR loops.

heLen