Calculations For Counting and Grand Total

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Fields: stocknumber,qtyrequested,unit price,GrandtotalCost
I need to get the total amount for each stocknumber requested then the Total
Cost for each month.

Example
stocknumber:16672
qtyRequested:500
Unit Price: $10,22
Grand Total $l51.10
I don't want to repeat the stocknumber over and over again.
How do I do this?
 
Cyndy,

Use a group by statement -

Select stocknumber, sum(qtyReuested) as SumqtyRequested , last(UnitPrice) as
LastUnitPrice, Sum(qtyRequested * UnitPrice) as GrandTotal
From TableName
Group by stocknumber

I assumed that the unit price would be the same for records with the same
stocknumber. You could use the function last, first, min, or max for this
part of the query as they will all generate the same result.

If the unit price can differ, then add it to the group by statement, and
remove the 'last()' function.

Jim
 
Thank You for your help,this works great.
Jim C. said:
Cyndy,

Use a group by statement -

Select stocknumber, sum(qtyReuested) as SumqtyRequested , last(UnitPrice) as
LastUnitPrice, Sum(qtyRequested * UnitPrice) as GrandTotal
From TableName
Group by stocknumber

I assumed that the unit price would be the same for records with the same
stocknumber. You could use the function last, first, min, or max for this
part of the query as they will all generate the same result.

If the unit price can differ, then add it to the group by statement, and
remove the 'last()' function.

Jim
Click to expand...
 
Back
Top