首页 \ 问答 \ MySQL查询计算每个组内的百分比(MySQL query to calculate percentage within each group)

MySQL查询计算每个组内的百分比(MySQL query to calculate percentage within each group)

 如何转换结果：  
week,day,hour,branch,sales
1,1,1,a,50
1,1,1,b,50
1,1,2,a,25
1,1,2,b,75
 
 进入这个：  
week,day,hour,branch,sales,percentage
1,1,1,a,50,50%
1,1,1,b,50,50%
1,1,2,a,25,25%
1,1,2,b,75,75%
 
 请注意，百分比按每小时分组。 换句话说，它不是所有销售的百分比，而只是每小时内的销售额。 我尝试了以下查询，但这给了我所有销售的百分比：  
SELECT week,day,hour,branch,sales,
(sales/(SELECT SUM(sales) FROM test))*100 AS Percentage
FROM test
GROUP BY week,day,hour,branch

How to convert this result: 
week,day,hour,branch,sales
1,1,1,a,50
1,1,1,b,50
1,1,2,a,25
1,1,2,b,75
 
Into this:  
week,day,hour,branch,sales,percentage
1,1,1,a,50,50%
1,1,1,b,50,50%
1,1,2,a,25,25%
1,1,2,b,75,75%
 
Note that the percentage is grouped by each hour. In other words, it is not the percentage of all the sales but just the sales within each hour. I have tried the following query but this gives me percentage of all the sales: 
SELECT week,day,hour,branch,sales,
(sales/(SELECT SUM(sales) FROM test))*100 AS Percentage
FROM test
GROUP BY week,day,hour,branch

原文：https://stackoverflow.com/questions/33490620

更新时间：2023-10-03 22:10

最满意答案

 你根本不需要使用.select -  
 工作表代码：  
Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Sheet1.Range("A1:B2"), Target) Is Nothing Then
    MsgBox "Hello"
End If

End Sub
 
 模块代码：  
Sub DisplayResult()

Sheet1.Range("A3").Value = Sheet1.Range("A1").Value + Sheet1.Range("A2").Value
Sheet1.Range("B3").Value = Sheet1.Range("B1").Value + Sheet1.Range("B2").Value

End Sub
 
 如果将Call DisplayResult行放在Worksheet_Change子例程中而不限制应该触发的范围，那么它将最终进入无限循环（DisplayResult触发Worksheet_Change子）。 应该何时触发DisplayResult代码？ 只有当您更改了范围A1：B2？ 

you don't need to use .select at all here -  
Worksheet Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Sheet1.Range("A1:B2"), Target) Is Nothing Then
    MsgBox "Hello"
End If

End Sub
 
Module Code: 
Sub DisplayResult()

Sheet1.Range("A3").Value = Sheet1.Range("A1").Value + Sheet1.Range("A2").Value
Sheet1.Range("B3").Value = Sheet1.Range("B1").Value + Sheet1.Range("B2").Value

End Sub
 
If you put the Call DisplayResult line in your Worksheet_Change sub routine without limiting what range it should be triggered on, then it will end up going into an infinite loop (DisplayResult triggers the Worksheet_Change sub). When should the DisplayResult code be triggered? Only when you have changed Range A1:B2 ?

MySQL查询计算每个组内的百分比(MySQL query to calculate percentage within each group)

最满意答案

相关问答

Excel 2007 PC VBA - 错误6溢出(Excel 2007 PC VBA — Error 6 Overflow)[2022-12-04]

VBA输出写入单元格 - #VALUE！(VBA Output Write To Cell - #VALUE! Error)[2022-03-06]

如何在不同的单元格上输出结果？(How to output the result on different cell? (VBA Excel 2007))[2023-03-22]

Excel VBA：在每个输出下面添加一个空白单元格(Excel VBA: add a blank cell below every output)[2023-08-03]

如何从VBA写入单元格(how to write to a cell from VBA)[2022-03-13]

如何在php Excel 2007中读取没有格式的单元格(How to read cells without format in php Excel 2007)[2022-04-08]

不带VBA的不同单元格的公式输出结果(Output result of formula on different cell without VBA)[2022-12-28]

在Excel单元格中调用正则表达式(Call regex in an Excel cell)[2024-01-04]

VBA条件单元格选择(VBA conditional cell selection)[2024-03-07]

使用vba比较excel中的2个单元格(compare 2 cells in excel by using vba)[2022-06-13]

相关文章

最新问答