sql - 添加总计行并转换为百分比

Question

我有一个数据库，其中包含有关人们的工作条件和社区的信息。

我必须像这样以百分比显示信息图表:

Neighbourhood Total  Employed Unemployed Inactive
Total          100     50         25        25
1              100     45         30        25
2              100     55         20        25

为此，我到目前为止编写的代码是:

   select neighbourhood, Count (*) as Total,
   Count(Case when (condition = 1) then 'employed' end) as employed,
   Count (case when (condition = 2) then 'unemployed' end) as unemployed,
   Count (Case when (condition =3) then 'Inactive' end) as Inactive

   from table
   group by  neighbourhood
   order by  neighbourhood

该代码的输出是(绝对数字是组成的，它们不会产生上述百分比):

   Neighbourhood Total  Employed Unemployed Inactive
   1              600     300        200        100
   2              450     220        159        80

因此，我必须将绝对数字转换为百分比并添加总行(从邻域中求和值)，但我所有的努力都失败了。我无法解决如何添加该总计行，也无法解决如何为每个社区计算百分比的总计

我两周前才开始学习 SQL，对于给您带来的不便，我深表歉意。我尽力保持简单(在我的数据库中有 15 个社区，如果它们用数字标记就可以了)

谢谢

Answer 1

需要UNION来添加总行

   select 'All' as neighbourhood, Count (*) as Total,
   Count(Case when (condition = 1) then 1 end) as employed,
   Count (case when (condition = 2) then 1 end) as unemployed,
   Count (Case when (condition =3) then 1 end) as Inactive

   from table

   UNION all

   select neighbourhood, Count (*) as Total,
   Count(Case when (condition = 1) then 1 end) as employed,
   Count (case when (condition = 2) then 1 end) as unemployed,
   Count (Case when (condition =3) then 1 end) as Inactive

   from table
   group by  neighbourhood
   order by  neighbourhood