→ fantasyj:select sum(amount) as a, avg(amount) as b from table 09/15 08:54
→ pttsasho:這樣求出來的應該是平均值吧！ 我想查詢的是各別客戶的 09/15 09:26
→ pttsasho:比重。比方說A客戶比重為：10/60 = 0.167 這樣，謝謝! 09/15 09:27
→ fantasyj:select a.customer, a.amount, a.amount / b.toal_amt 09/15 18:33
→ fantasyj: from table as a, 09/15 18:34
→ fantasyj: (select sum(b.amount) as total_amt 09/15 18:34
→ fantasyj: from table as b) as b 09/15 18:34
感謝F大的指點 受教了
另外這邊還有個問題 假設我的表格如下：
Customer Amount
-----------------------
A 10
A 11
A 12
B 20
B 21
B 22
C 30
同樣是希望能夠算出個別客戶的比重
但不知為什麼我的敘述查詢出來的結果不太對 是否可請各位前輩幫忙看一下
select a.customer,sum(a.amount),sum(a.amount)/b.total
from table as a, (select sum(b.amount) as total_amt from table as b) as b
group by a.customer
不知道為什麼 sum(a.amount)出來的結果與實際數值不符
還請各位前輩幫忙
感謝
--
※ 發信站: 批踢踢實業坊(ptt.cc)
◆ From: 118.171.212.181
※ 編輯: pttsasho 來自: 118.171.212.181 (09/17 12:49)
※ 編輯: pttsasho 來自: 118.171.212.181 (09/17 12:51)
推 fantasyj:select a.customer, a.amt, a.amt/b.total_amt 09/17 14:30
推 fantasyj:from (select a.customer,sum(a.amount) as amt from tab 09/17 14:32
→ fantasyj:le as a group by a.customer) as a, 09/17 14:33
→ fantasyj:(select sum(b.amount) as total_amt from table as b) 09/17 14:34
→ fantasyj:上面的拼起來執行看看(用手機不太好回文) 09/17 14:35