Search code examples
sqloracle-databaseoracle12c

ORACLE query with percentage of total for categories and subcategories

Query :

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

over my table returns:

> 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"

I need to add columns for totals and percentages for sectors and subsectors in following way:

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"

I guess I need 2 variables that will hold values for overall total and total for each sector and than calculate percentage for sectors and subsectors but I don'k now how to formulat that.

Solution

  • you could use a join 

    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