Original posted: http://community.9cbs.net/expert/topic/3739/3739565.xml? Temp = .7632105 Table three fields | --------------- ---------------------------- || Product quantity unit price || =============== ====================================== || A 3 20 || A 4 25 | | a 2 30 | | B 6 85 | | B 3 96 || ------------------------------------------- - |
Now I want to get the following results: the average price of the product A ****** b ****** Note: A average price of a commodity
Average algorithm: A average price = (3 * 20) (4 * 25) (2 * 30) / (3 4 2), the average of B is as A.
Ask the SQL statement. Create Table Table (Product VARCHAR (5), Quantity INT, Unique Decimal (4, 2)) Insert Table Select 'A', 3, 20Union All Select 'A', 4, 25UNION All Select 'A', 2,30Union All Select 'B', 6,85UNION All Select 'B', 3,96
SELECT Products, Cast (Sum (ISNULL, 0) * ISNULL (Quantity, 0)) / SUM (Quantity) AS Decimal (4, 2)) AS 'Average' from Table Group BY Product
DROP TABLE Table
--result:
(The number of rows affects is 5 lines)
Product average ---------- A 24.44B 88.67
(The number of rows affects is 2 lines)
