Finding Percentages Based on Column Sum

  • 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

  • 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!

  • Jacob Wilkins - Friday, August 10, 2018 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!

    Hi Jacob, exactly what i want.
    Any ideas please
    Thanks

  • 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!

  • Jacob Wilkins - Friday, August 10, 2018 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!

    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