[RBASE-L] - RE: Pure SQL thought

[Jim Limburg]

Thanks Albert

I'll give that a try in the morning.. I never thought of using a view.
Very creative...
Jim Limburg

Albert Berry wrote:
Hey, Jim. Create a view for the summation, and it will work like a charm.

CREATE VIEW vwCatSum (Category,CostTotal) AS +
  SELECT Category,SUM(Quantity*CostAtPurchase) +
  FROM CostAtPrchas +
  GROUP BY Category
UPDATE SummaryTable +
  SET LstYrTotalCost = t2.CostTotal +
  FROM vwCatSum t2, LstYrTotalCost t1 +
  WHERE t1.Category = t2.Category


Jim Limburg <[EMAIL PROTECTED]> wrote:


G-Day all

I have a two column table that has one column populated with inventory 
categories called categories and one that holds lstyrtotalcst.. (Last Years 
Total Costs)

I have another table that has category (based off above table), quantity, and 
costatprchas (cost at purchase). This table is items purchased during the year
and the category is picked the cost is put in at the time it was purchase and
of course how many purchased

I need to update the Last Years Cost with a sumation of the quantity * 
costatprchas  for each row. Each rows cost is likely different so I can't just
determine it easily.

I have thought of developing a double cursor system to tally it up, but it's
not really flowing easily down this road either.
Is there a good SQL clause like:

update totalstable set styrtotalcst = (select sum(quantity * costatprchas ) 
where category = category..

or something similar

Jim Limburg