如何在SQL查询中实现总和百分比 [英] How I can achieve total and percentage with in SQL query

问题描述

当我计算百分比时，它只显示位置明智百分比。但我想要区域总和&视野中的百分比。

否则我必须做出不同的查询Region Wise&位置明智..

 我在SQL中有如下数据 
区域Loc数量上限％
南CHN 10 15 66.66666667 
南BLR 20 25 80 
南COK 30 35 85.71428571 
北CHD 10 15 66.66666667 
 North PUN 20 25 80 
 North DER 40 65 61.53846154 

 所需的输出如下 
区域Loc数量上限％< / small> 
南CHN 10 15 66.66666667 
南BLR 20 25 80 
南COK 30 35 85.71428571 
南部总计60 75 80 
北部CHD 10 15 66.66666667 
北PUN 20 25 80 
 North DER 40 65 61.53846154 
北总计70 105 66.66666667 

我是什么尝试过：

< pre lang =SQL>选择LocCode，（数量/容量）* 100来回我的Table group by LocCode 

 选择区域，（数量/容量） * 100来自表 组 按区域

 但我只能在两个不同的查询中实现这一点区域明智％和位置明智％ 。是他们获得单一查询的方式 
 
 Loc数量上限％
 CHN 10 15 66.66666667 
 BLR 20 25 80 
 COK 30 35 85.71428571 
 CHD 10 15 66.66666667 
 PUN 20 25 80 
 DER 40 65 61.53846154 
 ------------------ 
地区数量上限％
南60 75 80 
北70 105 66 

解决方案

如果我理解你的问题，您只想将结果放在一个查询中。为此，您可以使用 UNION ALL 。类似

 选择 '  Locations'，LocCode，（数量/容量）* 100 
 来自 myTable 
  group   by  ' 位置，LocCode 
  UNION   ALL  
 < span class =code-keyword>选择 ' 区域，区域，（数量） /容量）* 100 
 来自 myTable 
  group   by  '  Regions'，Region 

When I calculate percentage it only shows Location Wise Percent .But i want Regional total & percent in view.
else i have to make different query Region Wise & Location Wise..

    I have Data like below in SQL    
Region	Loc     Qty	Cap	%
South	CHN	10	15	66.66666667
South	BLR	20	25	80
South	COK	30	35	85.71428571
North	CHD	10	15	66.66666667
North	PUN	20	25	80
North	DER	40	65	61.53846154

 Desired Output  like below
Region	Loc     Qty	Cap	%</small>
South	CHN	10	15	66.66666667
South	BLR	20	25	80
South	COK	30	35	85.71428571
South Total		60	75	80
North	CHD	10	15	66.66666667
North	PUN	20	25	80
North	DER	40	65	61.53846154
North Total	70	105	66.66666667

What I have tried:

<pre lang="SQL">Select LocCode , (Qty/Capacity)*100 fro my Table group by LocCode 

Select Region , (Qty/Capacity)*100 fro my Table group by Region 

But i am only able achieve this in two different query Region wise % and Location Wise % . Is their a way to get in single query 

Loc     Qty	Cap	%
CHN	10	15	66.66666667
BLR	20	25	80
COK	30	35	85.71428571
CHD	10	15	66.66666667
PUN	20	25	80
DER	40	65	61.53846154
    ------------------
Region	Qty	Cap	%
South	60	75	80
North	70	105	66

解决方案

If I understand your question correctly, you want just to put the results together in a single query. For that you can use UNION ALL. Something like

Select 'Locations', LocCode, (Qty/Capacity)*100 
from myTable 
group by 'Locations', LocCode 
UNION ALL
Select 'Regions', Region,  (Qty/Capacity)*100 
from myTable 
group by 'Regions', Region

这篇关于如何在SQL查询中实现总和百分比的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！