时间戳

首页 > 解决方案 > ORACLE 查询，包含类别和子类别的总数百分比

sql - ORACLE 查询，包含类别和子类别的总数百分比

问题描述

询问 ：

SELECT Year, Month,Sector, Subsector, sum(employed), sum(unemployed)
FROM dbo.workforce
where Year= 2017 and Month = 12 and Sector = any('0700','0500','0600')
group by Year, Month,Sector, Subsector

在我的桌子上返回：

> Year,Month,Sector,Subsector,SUM(Employed),SUM(Unemployed)
> "2017","12","0700","0720","30089","2348"
> "2017","12","0600","0630","16778","781"
> "2017","12","0500","0000","7332","1198"
> "2017","12","0600","0620","3741","338"
> "2017","12","0700","0710","56308","4493"
> "2017","12","0600","0610","105492","21966"

我需要以下列方式添加部门和子部门的总数和百分比列：

Year,Month,Sector,TotalSector,PercentageFromAllSectors,SubSector,TotalForSubsector,PercentageForSubsector
"2017","12","0700","6845","3,65","0720","2351","34,35"
"2017","12","0700","6845","3,65","0710","4494","65,65"

我想我需要 2 个变量来保存每个部门的总和总计值，然后计算部门和子部门的百分比，但我现在不知道如何制定它。

标签： sqloracleoracle12c

解决方案

你可以使用连接 

select  a.Year, a.Month, a.Sector, a.Subsector, a.sum_employed
    , (a.sum_employed/b.tot_employed)*100, a.sum_unemployed, (a.sum_unemployed/b.tot_unemployed)*100
from (
  SELECT Year, Month,Sector, Subsector, sum(employed) sum_employed, sum(unemployed) sum_unemployed
  FROM dbo.workforce
  where Year= 2017 and Month = 12 and Sector = any('0700','0500','0600')
  group by Year, Month,Sector, Subsector
  ) a
  inner join  (
  SELECT Year, Month,Sector,sum(employed) tot_employed, sum(unemployed) tot_unemployed
  FROM dbo.workforce
  where Year= 2017 and Month = 12 and Sector = any('0700','0500','0600')
  group by Year, Month,Sector
)  b  on a.Year = b.Year 
        and a.Month = b.Month 
          and a.Sector = b.Sector

推荐阅读