时间戳

首页 > 解决方案 > 在 SQL Server 中根据列总和查找百分比

sql - 在 SQL Server 中根据列总和查找百分比

问题描述

链接到我想要的图片 大家好，我需要有关在列上查找百分比的帮助。我想根据下面示例数据中的基列查找每行的百分比：

CREATE TABLE [dbo].[TestData](
[Selection] [nvarchar](150) NULL,
[Criteria] [nvarchar](500) NULL,
[Heading] [nvarchar](550) NOT NULL,
[Base] [int] NULL,
[WhatImGetting] [numeric](5, 0) NULL,
[WhatIWant] [numeric](5, 0) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[TestData] ([Selection], [Criteria], [Heading], [Base],    [WhatImGetting], [WhatIWant]) VALUES (N'All', N'All', N'', 5, CAST(100 AS Numeric(5, 0)), CAST(100 AS Numeric(5, 0)))
GO
INSERT [dbo].[TestData] ([Selection], [Criteria], [Heading], [Base], [WhatImGetting], [WhatIWant]) VALUES (N'Gender', N'Male', N'', 5, CAST(100 AS Numeric(5, 0)), CAST(100 AS Numeric(5, 0)))
GO
INSERT [dbo].[TestData] ([Selection], [Criteria], [Heading], [Base], [WhatImGetting], [WhatIWant]) VALUES (N'Location', N'Abuja', N'', 2, CAST(40 AS Numeric(5, 0)), CAST(40 AS Numeric(5, 0)))
GO
INSERT [dbo].[TestData] ([Selection], [Criteria], [Heading], [Base], [WhatImGetting], [WhatIWant]) VALUES (N'Location', N'Kano', N'', 3, CAST(60 AS Numeric(5, 0)), CAST(60 AS Numeric(5, 0)))
GO
INSERT [dbo].[TestData] ([Selection], [Criteria], [Heading], [Base], [WhatImGetting], [WhatIWant]) VALUES (N'MaritalStatus', N'Married', N'', 2, CAST(40 AS Numeric(5, 0)), CAST(40 AS Numeric(5, 0)))
GO
INSERT [dbo].[TestData] ([Selection], [Criteria], [Heading], [Base], [WhatImGetting], [WhatIWant]) VALUES (N'MaritalStatus', N'Single', N'', 3, CAST(60 AS Numeric(5, 0)), CAST(60 AS Numeric(5, 0)))
GO
INSERT [dbo].[TestData] ([Selection], [Criteria], [Heading], [Base], [WhatImGetting], [WhatIWant]) VALUES (N'Question1', N'3yrs – 4 yrs.', N'Q1. How long has this business existed?', 3, CAST(60 AS Numeric(5, 0)), CAST(75 AS Numeric(5, 0)))
GO
INSERT [dbo].[TestData] ([Selection], [Criteria], [Heading], [Base], [WhatImGetting], [WhatIWant]) VALUES (N'Question1', N'16yrs – 20yrs', N'Q1. How long has this business existed?', 1, CAST(20 AS Numeric(5, 0)), CAST(25 AS Numeric(5, 0)))
GO

这是我的示例代码

with cte as (
Select 
  Selection,
  Criteria, 
  Heading,
  Base 
from TestData 
)
Select Selection, 
  Criteria, 
  Heading, 
  Base, 
  cast(round(Base * 100.0/Nullif(
               (select 
                 SUM(case when Selection = 'Gender' then Base Else 0 End) 
               from cte),0),0) as numeric(5,0)) Male  
from cte

其他都是正确的，到了选择下的Question1时计算错误，显然所有的计算都是错误的。

显然我想做的是根据选择列中的值对基数求和，并使用求和来计算百分比，例如对于总和为 4 的 Question1，百分比将为 Base * 100/4，这将给我 25% 和75%

谢谢

蒂姆

标签： sqlsql-servercommon-table-expression

解决方案

The issue is, you have sumon basecolumn when selection is genderwhich will be equal to 5.

SUM(case when Selection = 'Gender' then Base Else 0 End

现在，当您计算基数为 3 的“问题 1 - 3 年 - 4 年”的百分比时，它将达到 60%，而不是您预期的 75%。(3*100)/5

您假设SUM通过对选择进行分组并将其用作总数来计算组内每一行的百分比。我已经修改了您的查询，如下所示，这应该可以按照您的问题的预期工作

with cte as (
Select 
  Selection,
  SUM(BASE) as SelSum -- Sum for each selection group
from TestData 
group by selection
)
Select t.Selection, 
  Criteria, 
  Heading, 
  Base, 
  cast(round(Base * 100.0/Nullif(c.SelSum,0),0) as numeric(5,0)) Male  
from TestData t
inner join cte c on t.selection = c.selection

输出

Selection       Criteria        Heading                                 Base Male
All             All                                                     5       100
Gender          Male                                                    5       100
Location        Abuja                                                   2       40
Location        Kano                                                    3       60
MaritalStatus   Married                                                 2       40
MaritalStatus   Single                                                  3       60
Question1       3yrs – 4 yrs.   Q1. How long has this business existed? 3       75
Question1       16yrs – 20yrs   Q1. How long has this business existed? 1       25

推荐阅读