How do I get the total number of columns from the sum of the quantity and the average price?
I am working on Ms Access database and there is a table called "Data Table" and the data contained in this table is the stock of the business sold and the columns are Item, Quantity, Price, Total and I used the following aggregate function to group the results of selecting 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 did 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 the first concept.
source to share
Yours Total Formula
is wrong from a mathematical point of view. Or yours Price Formula
:
Consider buying 1 item of type ABC for 0€ each
AND buying 2 items of type XYZ for 1€ each
. The expected total should be 2€
. But if you calculate the average price like you do, you get the price (0€ + 1€) / 2 = 0.5€
(note that you divide 2 by the number of different items). If you decrease the number of items you bought ( 3
), you get the price1.5€ != 2€
This is because your average is simply averaging the price of each item, ignoring the quantity.
You will need to understand what you have to represent average price
. It has no real meaning in any way and should probably be obtained by itself at the total price. I can't think of a correct way to generate average pricing without calculating the total value implicitly. It should be simple SUM([Quanity] * [Price]) / SUM([Quanity])
, which is for sureTotal / Quanity
source to share
Try using this SQL statement in your 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. Should have given columns different names to avoid errors (e.g. quantity becomes sum)
source to share