August 10, 2018 at 3:31 pm
Good day all,
Please i need help concerning finding percentage on a column. I want to find percentage of each row based on the Base Column in the sample data below:
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
This is my sample code
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
Others are correct, its when it gets to Question1 under selection that the calculation is wrong, obvious all the calculation is wrong.
Thanks
Tim
August 10, 2018 at 3:50 pm
It's not entirely clear from your description, but from your expected results it looks like maybe what you're wanting is this:
For each row, report the percentage its Base value is of the total Base reported for that value of Selection.
Is that accurate?
Cheers!
August 10, 2018 at 4:02 pm
Jacob Wilkins - Friday, August 10, 2018 3:50 PMIt's not entirely clear from your description, but from your expected results it looks like maybe what you're wanting is this:For each row, report the percentage its Base value is of the total Base reported for that value of Selection.
Is that accurate?
Cheers!
Hi Jacob, exactly what i want.
Any ideas please
Thanks
August 10, 2018 at 4:08 pm
In that case, something like this should do the trick:
SELECT *,
percentage_of_selection=Base*100.0/SUM(Base) OVER (PARTITION BY Selection)
FROM TestData;
Cheers!
August 10, 2018 at 4:28 pm
Jacob Wilkins - Friday, August 10, 2018 4:08 PMIn that case, something like this should do the trick:
SELECT *,
percentage_of_selection=Base*100.0/SUM(Base) OVER (PARTITION BY Selection)
FROM TestData;Cheers!
Hi Jacob,
Thanks very much, i totally forgot about the work of partitioning.
Thanks once again
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply