英文:
How to get the sum Percentage of duplicate field name
问题
在我的OrderDetails表中，为每一行获取百分比金额的操作正常运行。我希望将重复的产品名称行合并为一个百分比。我尝试了两种不同的方法，但不知道如何应用分组或其他适当的方法来实现这一点。请帮助。
| ID | 产品 | 金额 |
|---|---|---|
| 1 | 香蕉 | 2.00 |
| 2 | 木瓜 | 4.00 |
| 3 | 香蕉 | 2.00 |
使用以下代码：
select Product, SUM(Amount) as Amount
from OrderDetails
group by Product
预期输出应该是：
| ID | 产品 | 金额 |
|---|---|---|
| 1 | 香蕉 | 4.00 |
| 2 | 木瓜 | 4.00 |
英文:
In my OrderDetails table getting the percentage Amount for each row works fine. I want the duplicate Product name row to combine its percentage as one. I have tried two difference ways but don't know how to apply Group or any other suitable method to accomplish thisl Kindly assist
| ID | Product | Amount |
|---|---|---|
| 1 | Banana | 2.00 |
| 2 | Pawpaw | 4.00 |
| 3 | Banana | 2.00 |
this
select Product, Amount,
(Amount * 100 / sum(Amount) over ()) as Percentage
from OrderDetails
And this
SELECT Product, Amount / (SELECT SUM(Amount) FROM OrderDetails ) *100 AS AmountPercentage
FROM OrderDetails
Output from the codes above
| ID | Product | Amount |
|---|---|---|
| 1 | Banana | 25 |
| 2 | Pawpaw | 50 |
| 3 | Banana | 25 |
My expected output sgould be
| ID | Product | Amount |
|---|---|---|
| 1 | Banana | 50 |
| 2 | Pawpaw | 50 |
答案1
得分: 2
只需首先按Product进行GROUP BY。然后在聚合上执行窗口函数，即SUM(SUM(..)) OVER ()。
SELECT
MIN(od.ID) AS ID,
od.Product,
SUM(od.Amount) * 100.0 / SUM(SUM(od.Amount)) OVER () AS Amount
FROM OrderDetails od
GROUP BY
od.Product;
ID 的要求不清楚，但我选择了MIN(ID)。
英文:
You just need to GROUP BY Product first. Then you do a window function over the aggregation ie SUM(SUM(..)) OVER ()
SELECT
MIN(od.ID) AS ID,
od.Product,
SUM(od.Amount) * 100.0 / SUM(SUM(od.Amount)) OVER () AS Amount
FROM OrderDetails od
GROUP BY
od.Product;
It's unclear what you want for ID but I've gone for MIN(ID)
答案2
得分: 0
你可以使用窗口函数：
select Id,Product,perAmount from (
select id, Product ,round( 100.0* sum(Amount) over (partition by Product) / sum(Amount) over () ,2) as perAmount
,ROW_NUMBER() over (partition by Product order by id) as rw
from Orderss
)a
where rw=1
带有 Group by 的查询：
select min(id) as id, Product ,round( 100.0* sum(Amount) / max( Sumall) ,2) as perAmount
from Orderss
inner join (select sum(Amount) Sumall from Orderss) a on 1=1
group by Product
英文:
you can use window function
select Id,Product,perAmount from (
select id, Product ,round( 100.0* sum(Amount) over (partition by Product) / sum(Amount) over () ,2) as perAmount
,ROW_NUMBER() over (partition by Product order by id) as rw
from Orderss
)a
where rw=1
with Group by
select min(id) as id, Product ,round( 100.0* sum(Amount) / max( Sumall) ,2) as perAmount
from Orderss
inner join (select sum(Amount) Sumall from Orderss) a on 1=1
group by Product
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论