sql-servert-sqlpercentagecalculated-field

How to find the percentages per category per another category


I am having trouble trying to generate a calculated column in SQL

My query is

SELECT 
p.PolicyNumber,
r.RiskType
r.RiskClass,
SUM(p.CommissionVAT + p.CommissionEXVAT) as Commission,
NULL as CommPerc
FROM policies p 
INNER JOIN risks r ON p.id = r.id
GROUP BY p.PolicyNumber, r.RiskType, r.RiskClass

Its ouput:

PolicyNumber     RiskType     RiskClass     Commission
pol_1            Building     non-motor     1000
pol_1            Content      non-motor     50
pol_1            Reg.Motor    motor         800
po1_1            Reg.Motor    motor         10
pol_2            Building     non-motor     200
pol_2            Reg.Motor    motor         60
pol_2            Reg.Motor    motor         40

I am trying to calculate the Commission Percentage, where I find the total of each RiskClass per PolicyNumber and then divide the individual amounts by that total

so for eg. for pol_1

PolicyNumber     RiskType     RiskClass     Commission  CommPerc
pol_1            Building     non-motor     1000        95%
pol_1            Content      non-motor     50          5%
pol_1            Reg.Motor    motor         800         44.44%
po1_1            Reg.Motor    motor         1000        55.55%

and so on... How do I achieve this with SQL?

Many thanks!


Solution

  • As Ross Bush mentioned you seem to have two values with identical groupings. What's the table key? Since, I couldn't groupby any key I created my own as uniqueId, which then gave the right results. So maybe you can use this and modify the rest..

    DECLARE @Table TABLE(
          policy_number varchar(10)
        , RiskType          varchar(20)
        , RiskClass         varchar(20)
        , Commission        int
    )
    
    INSERT INTO @Table
    SELECT           'pol_1', 'Building',  ' non-motor', 1000
    UNION SELECT 'pol_1', 'Content',   ' non-motor', 50
    UNION SELECT 'pol_1', 'Reg.Motor', ' motor',     800
    UNION SELECT 'pol_1', 'Reg.Motor', ' motor',     1000
    UNION SELECT 'pol_2', 'Building',  ' non-motor', 200
    UNION SELECT 'pol_2', 'Reg.Motor', ' motor',     60
    UNION SELECT 'pol_2', 'Reg.Motor', ' motor',     40
    
    -- With fictional key created by CTE
    ;WITH CTE AS(
        SELECT 
                policy_number
            , RiskType
            , RiskClass
            , Commission
            , CHECKSUM(policy_number, RiskType, RiskClass, Commission) as uniqueId
        FROM @Table
    )
    SELECT 
            policy_number
        , RiskType
        , RiskClass
        , ROUND(CAST(SUM(Commission) as float) / comissionTOT * 100, 2) as CommPerc
        , SUM(Commission) as Commission
        , comissionTOT
    FROM CTE t
    OUTER APPLY(SELECT ISNULL(SUM(Commission), 0) as comissionTOT
        FROM @Table it
        WHERE it.policy_number  = t.policy_number
        AND     it.RiskClass            = t.RiskClass
    ) OA
    WHERE policy_number = 'pol_1'
    GROUP BY uniqueId, policy_number, RiskType, RiskClass, comissionTOT
    
    /*
    policy_number   RiskType    RiskClass   CommPerc    Comission   comissionTOT
        pol_1               Building     non-motor  95,24           1000                1050
        pol_1               Reg.Motor    motor          44,44           800                 1800
        pol_1               Reg.Motor    motor          55,56           1000                1800
        pol_1               Content     non-motor       4,76            50                  1050
    */
    
    --Without any key
    SELECT 
            policy_number
        , RiskType
        , RiskClass
        , ROUND(CAST(SUM(Commission) as float) / comissionTOT * 100, 0) as CommPerc
    FROM @Table t
    OUTER APPLY(SELECT ISNULL(SUM(Commission), 0) as comissionTOT
        FROM @Table it
        WHERE it.policy_number  = t.policy_number
        AND     it.RiskClass            = t.RiskClass
    ) OA
    WHERE policy_number = 'pol_1'
    GROUP BY policy_number, RiskType, RiskClass, comissionTOT