working with SUM

  • Thread starter Thread starter shank
  • Start date Start date
S

shank

In the following query, I get a SumOfWeight value that is correct for each
record.
How can I get a value like Sum(SumOfWeight)?
I want the TOTAL weight of all products in the recordset.
thanks!

SELECT Cart.OrderNo, Cart.SoftHard, Sum(Cart.Qty) AS SumOfQty,
Cart.Description, Sum(Cart.Weight) AS SumOfWeight
FROM Cart
GROUP BY Cart.OrderNo, Cart.SoftHard, Cart.Description;
 
Dear Shank:

If a query is going to show this sum for an entire table, it can't be
broken down by OrderNo, SoftHard, etc. The query gets much simpler:

SELECT Sum(Qty) AS SumOfQty, Sum(Cart.Weight) AS SumOfWeight
FROM Cart

This will produce sums for the entire table.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Hi shank,

I have to get to work, so untested,
the following may be an alternative
to Tom's suggestion:

SELECT Cart.OrderNo, Cart.SoftHard, Sum(Cart.Qty) AS SumOfQty,
Cart.Description, Sum(Cart.Weight) AS SumOfWeight,
Max((SELECT Sum(C.Weight) FROM Cart As C)) As TotalWeight
FROM Cart
GROUP BY Cart.OrderNo, Cart.SoftHard, Cart.Description;

"Max" isn't really operating as "max" but is
just bringing the single value "through,"....
it could have just as easily been another
aggregate like "First."

This will be total for the table (not sure what you
mean by "recordset").

Good luck,

Gary Walter
 
Back
Top