How to get total of multiple columns from sum of quantity and average of price?

Question

I am working on an Ms Access Database and there is a table called "Data Table" and the data this table contains is sold stock of a business and the columns are Item, Quantity, Price, Total and I used the following aggregate functions for grouping the select results of this table:

Quantity: =Sum([Quantity])       Result = 23
Price:    =Avg([Price])          Result = 4.5
Total:    =[Quantity] * [Price]  Result = 103.5

The problem is that the result is not the same as if I do the following calculation:

Quantity: =Sum([Quantity])       Result = 23
Price:    GROUP BY [Price]       Result = Different Columns
Total:    =[Quantity] * [Price]  Result = Different Columns
Grand Total: =Sum([Total])       Result = 110

And the result of this 110 is the exact true value I want this result to get with the first concept.

Answer 1

Your Total Formula is just wrong from a mathematical standpoint. Or your Price Formula:

Consider buying 1 item of type ABC for 0€ each AND buying 2 items of type XYZ for 1€ each. The abvious total sum should be 2€. But if you calculate the average price as you do you will get a price of (0€ + 1€) / 2 = 0.5€ (Note that the 2 you divide by is the number of different items). If you mutliple that by the amount of items you bought (3) you will end up with a price of 1.5€ != 2€

That is because your average is just averaging over the price of every item ignoring its quantity.

You will have to get clear what the average price should represent. It has no real meaning any way and should probably be itself derived by the total price. I cannot think of a proper way to generate the average pricing without calculating the total value implicitly. It should just be SUM([Quanity] * [Price]) / SUM([Quanity]) which is exactly Total / Quanity

Answer 2

Try using this SQL Statement in a Query:

SELECT [Data Table].Item, Sum([Data Table].Quantity) AS [Sum Quantity], Avg([Data Table].Price) AS [AVG Price], Sum([Quantity]*[Price]) AS Total
FROM [Data Table]
GROUP BY [Data Table].Item;

Note: Had to give the columns Different Names to avoid errors.(eg. Quantity becomes Sum Quantity)