SELECT aa.品號, aa.售價/aa.筆數
( SELECT 品號, SUM(售價) AS 售價, COUNT(*) AS 筆數
FROM 每月統計檔
GROUP BY 品號 ) aa
WHERE aa.筆數 > 0
SAP B1 報表 與 查詢 大量使用 MSSQL
歡迎大家技術交流
技術轉移顧問
Albert
<pre class="c" name="code">select 品號,sum(售價)/count(*) as 平均售價 from 每月統計檔 group by 品號
<pre class="c" name="code">
用MySQL測試一下...
CREATE TABLE ithelp120302 (
PartID CHAR(2) NOT NULL,
SaleDate CHAR(6) NOT NULL,
Price INT NOT NULL,
PRIMARY KEY (PartID, SaleDate)
);
INSERT INTO ithelp120302 VALUES
('AA', '201112', 100),
('BB', '201112', 80),
('AA', '201201', 110),
('CC', '201201', 60),
('AA', '201202', 90),
('BB', '201202', 90),
('CC', '201202', 70);
SELECT PartID, AVG(Price)
FROM ithelp120302
GROUP BY PartID;
+--------+------------+
| PartID | AVG(Price) |
+--------+------------+
| AA | 100.0000 |
| BB | 85.0000 |
| CC | 65.0000 |
+--------+------------+