使用以下查询:
SELECT
ACT_CODE,
ORL_EVENT,
ACT_NOTE,
(SUM(ORL_PRICE*ORL_ORDQTY)) AS 'ORD_TOTAL'
FROM
R5ORDERLINES
INNER JOIN
R5ACTIVITIES on ACT_EVENT = ORL_EVENT
WHERE
ORL_EVENT = '123'
GROUP BY
ACT_CODE, ORL_EVENT, ACT_NOTE
ORDER BY
ACT_ACT
我得到这样的结果,其中ORD_TOTAL是所有行的总和,而我需要每个ACT_CODE的总和(所有房屋、所有汽车等的总和(,而不是所有产品的总和。
+--------+---------+-----------+----------+
|ACT_CODE|ORL_EVENT| ACT_NOTE | ORD_TOTAL|
+--------+---------+-----------+----------+
|1110 |123 | Houses | 2500 |
|1111 |123 | Cars | 2500 |
|1112 |123 | TVs | 2500 |
|1113 |123 | Computers | 2500 |
+--------+---------+-----------+----------+
事实上,我需要它是这样的:
+--------+---------+-----------+----------+
|ACT_CODE|ORL_EVENT| ACT_NOTE | ORD_TOTAL|
+--------+---------+-----------+----------+
|1110 |123 | Houses | 1250 |
|1111 |123 | Cars | 500 |
|1112 |123 | TVs | 250 |
|1113 |123 | Computers | 500 |
+--------+---------+-----------+----------+
我尝试了很多查询修改,但似乎都不起作用。。。我不是这方面的专家。
使用PARTITION BY
子句。我修改了如下查询。没有测试,因为我没有数据。请检查并还原。
SELECT DISTINCT
ACT_CODE,
ORL_EVENT,
ACT_NOTE,
(SUM(ORL_PRICE*ORL_ORDQTY) OVER(PARTITION BY ACT_NOTE) AS 'ORD_TOTAL'
FROM
R5ORDERLINES
INNER JOIN
R5ACTIVITIES on ACT_EVENT = ORL_EVENT
WHERE
ORL_EVENT = '123'
ORDER BY
ACT_ACT