如何將SQL直行欄位加總完並放在指定欄位?

sql 加總

甜點螞蟻教主 2021-11-08 11:44:30 ‧ 2127 瀏覽

分享至

我使用的db是 Oracle client，已有試過使用sum、where、group by的指令，但還是錯誤，不知道要怎麼解了 @@a

因此要請教各位，如何把直行的數字由上往下的全加總起來(藍色框)，計算的結果需要放在此直行欄位的最底下，並對應第29格的欄位(紅色框)，謝謝各位。

其實我是要從兩個張不同的table合併資料並計算總計

原本只有28個項目，而總計要加入當第29個項目，並將投入量1加總，將總計的結果放在最底下欄位，對應到第29個項目"總計"。

補充我的資料庫字串及SQL的欄位圖示

with t1 as (
SELECT 分群, sum(重計投入量) 投入量1,sum(產出_M2) 入庫量1,  LEAST(sum(產出_M2)/sum(重計投入量)*100,100) 量產1
from tableName 
where FACTORY='@@' and 工單別='量產'  and 階數='1' and 結案日=to_date('20211107', 'yyyy-MM-dd')
group by 分群)

, t2 as (
SELECT 分群, sum(重計投入量) 發料量,sum(產出_M2) 入庫量2,sum(邊料回算_M2) 入庫量2回,  LEAST((sum(產出_M2)-sum(邊料回算_M2))/sum(重計投入量)*100,100) 量產2, LEAST(sum(產出_M2)/sum(重計投入量)*100,100) 量產2回
from tableName 
where FACTORY='@@' and 工單別='量產'  and 階數='2' and 結案日=to_date('20211107', 'yyyy-MM-dd')  
group by 分群)

,t3 as(SELECT 品目類別 from YIELD_NAME)

select t3.品目類別, round(t1.投入量1,0) 投入量1, round(t1.入庫量1,0) 入庫量1, round(t1.量產1,2) 量產1
, round(t2.發料量,0) 發料量, round(t2.入庫量2,0) - round(t2.入庫量2回,0) 入庫量2, round(t2.量產2,2) 量產2, 
round(t1.量產1*t2.量產2/100,2) 量產,round(t2.量產2回,2) 量產2回,
round(t1.量產1*t2.量產2回/100,2) 量產回,round(t2.入庫量2回,0) 工單入庫量2回
from t1 full join t2 on (t1.分群=t2.分群) full join t3 on(t1.分群=t3.品目類別) where t3.品目類別 is not null 

order by 1;

石頭 iT邦高手 1 級 ‧ 2021-11-08 11:51:17 檢舉

Waht's your dbms?

甜點螞蟻教主 iT邦新手 3 級 ‧ 2021-11-08 11:57:36 檢舉

我的db是用Oracle client

一級屠豬士 iT邦大師 1 級 ‧ 2021-11-08 12:34:39 檢舉

https://oracle-base.com/articles/misc/rollup-cube-grouping-functions-and-grouping-sets#rollup

https://ithelp.ithome.com.tw/articles/10136825

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

2 個回答

rogeryao

iT邦超人 7 級 ‧ 2021-11-08 12:49:46

最佳解答

CREATE TABLE employee
(
department VARCHAR(50) NOT NULL,
salary INT 
);

INSERT INTO employee
VALUES ('Sales', 5000);
INSERT INTO employee
VALUES ('HR', 6000);
INSERT INTO employee
VALUES ('IT', 7500);
INSERT INTO employee
VALUES ('Marketing', NULL);
INSERT INTO employee
VALUES ('Finance', 5500);
INSERT INTO employee
VALUES ('Sales', 8000);

SELECT COALESCE (department, '總計') AS Department,SUM(salary) AS Salary_Sum
FROM employee
GROUP BY ROLLUP (department)

Demo

回應 4
分享
檢舉

看更多先前的回應...收起先前的回應...

甜點螞蟻教主 iT邦新手 3 級 ‧ 2021-11-08 14:22:27 檢舉

如果是兩張不同的table，要作到你這種結果，是不是要select + where條件去拉兩張表進來?
謝謝

(我有更新補充我的問題內容在發文)

rogeryao iT邦超人 7 級 ‧ 2021-11-08 15:34:45 檢舉

Demo
SQL JOINS
用哪一種 JOIN 視實務而定

rogeryao iT邦超人 7 級 ‧ 2021-11-08 17:05:03 檢舉

-- 方法一 : 所有欄位皆加總
WITH M AS (
SELECT 
X.品目類別,
SUM(CASE WHEN 階數 = '1' THEN 重計投入量 ELSE 0 END) 投入量1,
SUM(CASE WHEN 階數 = '1' THEN 產出_M2 ELSE 0 END) 入庫量1,
SUM(CASE WHEN 階數 = '2' THEN 重計投入量 ELSE 0 END) 發料量,
SUM(CASE WHEN 階數 = '2' THEN 產出_M2 ELSE 0 END) 入庫量2,
SUM(CASE WHEN 階數 = '2' THEN 邊料回算_M2 ELSE 0 END) 入庫量2回
FROM YIELD_NAME X
LEFT JOIN tableName D ON X.品目類別=D.分群
/*
WHERE FACTORY = '@@'
AND 工單別 = '量產'
AND 結案日 = to_date('20211107', 'yyyy-MM-dd')
*/
GROUP BY X.品目類別
)
,K AS (
SELECT
M.品目類別,
M.投入量1,
M.入庫量1,
M.發料量,
M.入庫量2,
M.入庫量2回,
CASE WHEN NVL(M.投入量1,0)=0 THEN 0 ELSE LEAST(M.入庫量1 / M.投入量1 * 100, 100) END 量產1,
CASE WHEN NVL(M.發料量,0)=0 THEN 0 ELSE LEAST((M.入庫量2 - M.入庫量2回) / M.發料量 * 100, 100) END 量產2,
CASE WHEN NVL(M.發料量,0)=0 THEN 0 ELSE LEAST(M.入庫量2 / M.發料量 * 100, 100)END  量產2回
FROM M
)

SELECT
COALESCE (K.品目類別, '總計') 品目類別,
ROUND(SUM(K.投入量1), 0) 投入量1,
ROUND(SUM(K.入庫量1), 0) 入庫量1,
ROUND(SUM(K.量產1), 2) 量產1,
ROUND(SUM(K.發料量), 0) 發料量,
ROUND(SUM(K.入庫量2), 0) - ROUND(SUM(K.入庫量2回), 0) 入庫量2,
ROUND(SUM(K.量產2), 2) 量產2,
ROUND(SUM(K.量產1 * K.量產2) / 100, 2) 量產,
ROUND(SUM(K.量產2回), 2) 量產2回,
ROUND(SUM(K.量產1 * K.量產2回) / 100, 2) 量產回,
ROUND(SUM(K.入庫量2回), 0) 工單入庫量2回
FROM K
GROUP BY ROLLUP (K.品目類別);

-- 方法二 : 部分欄位加總
WITH M AS (
SELECT 
X.品目類別,
SUM(CASE WHEN 階數 = '1' THEN 重計投入量 ELSE 0 END) 投入量1,
SUM(CASE WHEN 階數 = '1' THEN 產出_M2 ELSE 0 END) 入庫量1,
SUM(CASE WHEN 階數 = '2' THEN 重計投入量 ELSE 0 END) 發料量,
SUM(CASE WHEN 階數 = '2' THEN 產出_M2 ELSE 0 END) 入庫量2,
SUM(CASE WHEN 階數 = '2' THEN 邊料回算_M2 ELSE 0 END) 入庫量2回
FROM YIELD_NAME X
LEFT JOIN tableName D ON X.品目類別=D.分群
/*
WHERE FACTORY = '@@'
AND 工單別 = '量產'
AND 結案日 = to_date('20211107', 'yyyy-MM-dd')
*/
GROUP BY X.品目類別
)
,K AS (
SELECT
M.品目類別,
M.投入量1,
M.入庫量1,
M.發料量,
M.入庫量2,
M.入庫量2回,
CASE WHEN NVL(M.投入量1,0)=0 THEN 0 ELSE LEAST(M.入庫量1 / M.投入量1 * 100, 100) END 量產1,
CASE WHEN NVL(M.發料量,0)=0 THEN 0 ELSE LEAST((M.入庫量2 - M.入庫量2回) / M.發料量 * 100, 100) END 量產2,
CASE WHEN NVL(M.發料量,0)=0 THEN 0 ELSE LEAST(M.入庫量2 / M.發料量 * 100, 100)END  量產2回
FROM M
)
,F1 AS (
SELECT
COALESCE (K.品目類別, '總計') 品目類別,
ROUND(SUM(K.投入量1), 0) 投入量1,
ROUND(SUM(K.入庫量1), 0) 入庫量1,
ROUND(SUM(K.發料量), 0) 發料量,
ROUND(SUM(K.入庫量2), 0) - ROUND(SUM(K.入庫量2回), 0) 入庫量2,
ROUND(SUM(K.入庫量2回), 0) 工單入庫量2回
FROM K 
GROUP BY ROLLUP (K.品目類別)
)
,F2 AS (
SELECT
K.品目類別 品目類別,
ROUND(K.量產1, 2) 量產1,
ROUND(K.量產2, 2) 量產2,
ROUND((K.量產1 * K.量產2) / 100, 2) 量產,
ROUND(K.量產2回, 2) 量產2回,
ROUND((K.量產1 * K.量產2回) / 100, 2) 量產回
FROM K 
)

SELECT F1.品目類別,
F1.投入量1,
F1.入庫量1,
F2.量產1,
F1.發料量,
F1.入庫量2,
F2.量產2,
F2.量產,
F2.量產2回,
F2.量產回,
F1.工單入庫量2回
FROM F1
LEFT JOIN F2 ON F1.品目類別=F2.品目類別

-- 方法三 : 部分欄位加總，【純真的人】用的 UNION 方法
WITH M AS (
SELECT 
X.品目類別,
SUM(CASE WHEN 階數 = '1' THEN 重計投入量 ELSE 0 END) 投入量1,
SUM(CASE WHEN 階數 = '1' THEN 產出_M2 ELSE 0 END) 入庫量1,
SUM(CASE WHEN 階數 = '2' THEN 重計投入量 ELSE 0 END) 發料量,
SUM(CASE WHEN 階數 = '2' THEN 產出_M2 ELSE 0 END) 入庫量2,
SUM(CASE WHEN 階數 = '2' THEN 邊料回算_M2 ELSE 0 END) 入庫量2回
FROM YIELD_NAME X
LEFT JOIN tableName D ON X.品目類別=D.分群
/*
WHERE FACTORY = '@@'
AND 工單別 = '量產'
AND 結案日 = to_date('20211107', 'yyyy-MM-dd')
*/
GROUP BY X.品目類別
)
,K AS (
SELECT
M.品目類別,
M.投入量1,
M.入庫量1,
M.發料量,
M.入庫量2,
M.入庫量2回,
CASE WHEN NVL(M.投入量1,0)=0 THEN 0 ELSE LEAST(M.入庫量1 / M.投入量1 * 100, 100) END 量產1,
CASE WHEN NVL(M.發料量,0)=0 THEN 0 ELSE LEAST((M.入庫量2 - M.入庫量2回) / M.發料量 * 100, 100) END 量產2,
CASE WHEN NVL(M.發料量,0)=0 THEN 0 ELSE LEAST(M.入庫量2 / M.發料量 * 100, 100)END  量產2回
FROM M
)
,P AS (
SELECT
K.品目類別 品目類別,
ROUND(K.投入量1, 0) 投入量1,
ROUND(K.入庫量1, 0) 入庫量1,
ROUND(K.量產1, 2) 量產1,
ROUND(K.發料量, 0) 發料量,
ROUND(K.入庫量2, 0) - ROUND(K.入庫量2回, 0) 入庫量2,
ROUND(K.量產2, 2) 量產2,
ROUND(K.量產1 * K.量產2 / 100, 2) 量產,
ROUND(K.量產2回, 2) 量產2回,
ROUND(K.量產1 * K.量產2回 / 100, 2) 量產回,
ROUND(K.入庫量2回, 0) 工單入庫量2回
FROM K
)
 
SELECT *
FROM P
UNION
SELECT 
'總計' 品目類別,
SUM(投入量1) 投入量1,
SUM(入庫量1) 入庫量1,
NULL 量產1,
SUM(發料量) 發料量,
SUM(入庫量2) 入庫量2,
NULL 量產2,
NULL 量產,
NULL 量產2回,
NULL 量產回,
SUM(工單入庫量2回) 工單入庫量2回
FROM P

Demo

```
-- 方法一 : 所有欄位皆加總
WITH M AS (
SELECT 
X.品目類別,
SUM(CASE WHEN 階數 = '1' THEN 重計投入量 ELSE 0 END) 投入量1,
SUM(CASE WHEN 階數 = '1' THEN 產出_M2 ELSE 0 END) 入庫量1,
SUM(CASE WHEN 階數 = '2' THEN 重計投入量 ELSE 0 END) 發料量,
SUM(CASE WHEN 階數 = '2' THEN 產出_M2 ELSE 0 END) 入庫量2,
SUM(CASE WHEN 階數 = '2' THEN 邊料回算_M2 ELSE 0 END) 入庫量2回
FROM YIELD_NAME X
LEFT JOIN tableName D ON X.品目類別=D.分群
/*
WHERE FACTORY = '@@'
AND 工單別 = '量產'
AND 結案日 = to_date('20211107', 'yyyy-MM-dd')
*/
GROUP BY X.品目類別
)
,K AS (
SELECT
M.品目類別,
M.投入量1,
M.入庫量1,
M.發料量,
M.入庫量2,
M.入庫量2回,
CASE WHEN NVL(M.投入量1,0)=0 THEN 0 ELSE LEAST(M.入庫量1 / M.投入量1 * 100, 100) END 量產1,
CASE WHEN NVL(M.發料量,0)=0 THEN 0 ELSE LEAST((M.入庫量2 - M.入庫量2回) / M.發料量 * 100, 100) END 量產2,
CASE WHEN NVL(M.發料量,0)=0 THEN 0 ELSE LEAST(M.入庫量2 / M.發料量 * 100, 100)END  量產2回
FROM M
)

SELECT
COALESCE (K.品目類別, '總計') 品目類別,
ROUND(SUM(K.投入量1), 0) 投入量1,
ROUND(SUM(K.入庫量1), 0) 入庫量1,
ROUND(SUM(K.量產1), 2) 量產1,
ROUND(SUM(K.發料量), 0) 發料量,
ROUND(SUM(K.入庫量2), 0) - ROUND(SUM(K.入庫量2回), 0) 入庫量2,
ROUND(SUM(K.量產2), 2) 量產2,
ROUND(SUM(K.量產1 * K.量產2) / 100, 2) 量產,
ROUND(SUM(K.量產2回), 2) 量產2回,
ROUND(SUM(K.量產1 * K.量產2回) / 100, 2) 量產回,
ROUND(SUM(K.入庫量2回), 0) 工單入庫量2回
FROM K
GROUP BY ROLLUP (K.品目類別);
```
```
-- 方法二 : 部分欄位加總
WITH M AS (
SELECT 
X.品目類別,
SUM(CASE WHEN 階數 = '1' THEN 重計投入量 ELSE 0 END) 投入量1,
SUM(CASE WHEN 階數 = '1' THEN 產出_M2 ELSE 0 END) 入庫量1,
SUM(CASE WHEN 階數 = '2' THEN 重計投入量 ELSE 0 END) 發料量,
SUM(CASE WHEN 階數 = '2' THEN 產出_M2 ELSE 0 END) 入庫量2,
SUM(CASE WHEN 階數 = '2' THEN 邊料回算_M2 ELSE 0 END) 入庫量2回
FROM YIELD_NAME X
LEFT JOIN tableName D ON X.品目類別=D.分群
/*
WHERE FACTORY = '@@'
AND 工單別 = '量產'
AND 結案日 = to_date('20211107', 'yyyy-MM-dd')
*/
GROUP BY X.品目類別
)
,K AS (
SELECT
M.品目類別,
M.投入量1,
M.入庫量1,
M.發料量,
M.入庫量2,
M.入庫量2回,
CASE WHEN NVL(M.投入量1,0)=0 THEN 0 ELSE LEAST(M.入庫量1 / M.投入量1 * 100, 100) END 量產1,
CASE WHEN NVL(M.發料量,0)=0 THEN 0 ELSE LEAST((M.入庫量2 - M.入庫量2回) / M.發料量 * 100, 100) END 量產2,
CASE WHEN NVL(M.發料量,0)=0 THEN 0 ELSE LEAST(M.入庫量2 / M.發料量 * 100, 100)END  量產2回
FROM M
)
,F1 AS (
SELECT
COALESCE (K.品目類別, '總計') 品目類別,
ROUND(SUM(K.投入量1), 0) 投入量1,
ROUND(SUM(K.入庫量1), 0) 入庫量1,
ROUND(SUM(K.發料量), 0) 發料量,
ROUND(SUM(K.入庫量2), 0) - ROUND(SUM(K.入庫量2回), 0) 入庫量2,
ROUND(SUM(K.入庫量2回), 0) 工單入庫量2回
FROM K 
GROUP BY ROLLUP (K.品目類別)
)
,F2 AS (
SELECT
K.品目類別 品目類別,
ROUND(K.量產1, 2) 量產1,
ROUND(K.量產2, 2) 量產2,
ROUND((K.量產1 * K.量產2) / 100, 2) 量產,
ROUND(K.量產2回, 2) 量產2回,
ROUND((K.量產1 * K.量產2回) / 100, 2) 量產回
FROM K 
)

SELECT F1.品目類別,
F1.投入量1,
F1.入庫量1,
F2.量產1,
F1.發料量,
F1.入庫量2,
F2.量產2,
F2.量產,
F2.量產2回,
F2.量產回,
F1.工單入庫量2回
FROM F1
LEFT JOIN F2 ON F1.品目類別=F2.品目類別
```
```
-- 方法三 : 部分欄位加總，【純真的人】用的 UNION 方法
WITH M AS (
SELECT 
X.品目類別,
SUM(CASE WHEN 階數 = '1' THEN 重計投入量 ELSE 0 END) 投入量1,
SUM(CASE WHEN 階數 = '1' THEN 產出_M2 ELSE 0 END) 入庫量1,
SUM(CASE WHEN 階數 = '2' THEN 重計投入量 ELSE 0 END) 發料量,
SUM(CASE WHEN 階數 = '2' THEN 產出_M2 ELSE 0 END) 入庫量2,
SUM(CASE WHEN 階數 = '2' THEN 邊料回算_M2 ELSE 0 END) 入庫量2回
FROM YIELD_NAME X
LEFT JOIN tableName D ON X.品目類別=D.分群
/*
WHERE FACTORY = '@@'
AND 工單別 = '量產'
AND 結案日 = to_date('20211107', 'yyyy-MM-dd')
*/
GROUP BY X.品目類別
)
,K AS (
SELECT
M.品目類別,
M.投入量1,
M.入庫量1,
M.發料量,
M.入庫量2,
M.入庫量2回,
CASE WHEN NVL(M.投入量1,0)=0 THEN 0 ELSE LEAST(M.入庫量1 / M.投入量1 * 100, 100) END 量產1,
CASE WHEN NVL(M.發料量,0)=0 THEN 0 ELSE LEAST((M.入庫量2 - M.入庫量2回) / M.發料量 * 100, 100) END 量產2,
CASE WHEN NVL(M.發料量,0)=0 THEN 0 ELSE LEAST(M.入庫量2 / M.發料量 * 100, 100)END  量產2回
FROM M
)
,P AS (
SELECT
K.品目類別 品目類別,
ROUND(K.投入量1, 0) 投入量1,
ROUND(K.入庫量1, 0) 入庫量1,
ROUND(K.量產1, 2) 量產1,
ROUND(K.發料量, 0) 發料量,
ROUND(K.入庫量2, 0) - ROUND(K.入庫量2回, 0) 入庫量2,
ROUND(K.量產2, 2) 量產2,
ROUND(K.量產1 * K.量產2 / 100, 2) 量產,
ROUND(K.量產2回, 2) 量產2回,
ROUND(K.量產1 * K.量產2回 / 100, 2) 量產回,
ROUND(K.入庫量2回, 0) 工單入庫量2回
FROM K
)
 
SELECT *
FROM P
UNION
SELECT 
'總計' 品目類別,
SUM(投入量1) 投入量1,
SUM(入庫量1) 入庫量1,
NULL 量產1,
SUM(發料量) 發料量,
SUM(入庫量2) 入庫量2,
NULL 量產2,
NULL 量產,
NULL 量產2回,
NULL 量產回,
SUM(工單入庫量2回) 工單入庫量2回
FROM P
```
[Demo](https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=f0b57486b498f555522bffed394074d0)

修改

甜點螞蟻教主 iT邦新手 3 級 ‧ 2021-11-08 17:20:32 檢舉

好的，謝謝你的熱心解惑，我會再試試看

登入發表回應

純真的人

iT邦大師 1 級 ‧ 2021-11-08 11:52:59

這個其實要執行計算兩次~用union合起來~

declare @表格 table(
	類別 nvarchar(50)
	,投入量 int
)
select 類別
,Sum(投入量) 投入量
from @表格
group by 類別
union
select N'總計'
,Sum(投入量)
from @表格

回應 1
分享
檢舉

甜點螞蟻教主 iT邦新手 3 級 ‧ 2021-11-11 10:03:20 檢舉

謝謝你的解答分享

登入發表回應

我要發表回答

立即登入回答

參賽組數

1048 組

團體組數

40 組

累計文章數

14276 篇

最後報名日

9/15

15th鐵人賽 13th鐵人賽 14th鐵人賽 12th鐵人賽 16th鐵人賽 11th鐵人賽鐵人賽 2019鐵人賽 javascript 2018鐵人賽 python 2017鐵人賽 windows php c# windows server linux css react 資訊安全

IT邦幫忙