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