I have a simple query where I need to sum up all Premium per policy Number.
It gives me an error because I need to group it by Premium
.
Is any way to achieve this without grouping by "Premium"?
Sample query:
declare @Test table (PolicyNumber varchar(50), Coverage varchar(50), Premium money)
insert into @Test
select 'Pol1' as PolicyNumber, 'Coverage1' as Coverage, 100 as Premium
UNION ALL select 'Pol1', 'Coverage2', 200
UNION ALL select 'Pol1', 'Coverage2', 25
UNION ALL select 'Pol1', 'Coverage3', 500
UNION ALL select 'Pol1', 'Coverage4', 300
UNION ALL select 'Pol1', 'Coverage4', 25
UNION ALL select 'Pol1', 'Coverage5', 150
select
PolicyNumber,
Coverage,
SUM(Premium) as Premium,
-- this gives an error
PremiumPerPolicy = SUM(Premium) OVER (Partition by PolicyNumber)
from @Test
group by PolicyNumber, Coverage
The result should look like this
PolicyNumber Coverage Premium PremiumPerPolicy
Pol1 Coverage1 100 1300
Pol1 Coverage2 225 1300
Pol1 Coverage3 500 1300
Pol1 Coverage4 325 1300
Pol1 Coverage5 150 1300
You can aggregate your value normally and then aggerate again using a window function.
becomes