Hi,
I have a sample table of machines like the below

CREATE TABLE [dbo].[apps](
[Num] [float] NULL,
[Counts] [float] NULL,
[Links] [float] NULL,
[Result] [float] NULL,
[Code] [nvarchar](255) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[apps] ([Num], [Counts], [Links], [Result], [Code]) VALUES (3, 26, 18, 0.692307692307692, N'E')
INSERT [dbo].[apps] ([Num], [Counts], [Links], [Result], [Code]) VALUES (3, 27, 22, 0.814814814814815, N'G')
INSERT [dbo].[apps] ([Num], [Counts], [Links], [Result], [Code]) VALUES (3, 2, 2, 1, N'P')
INSERT [dbo].[apps] ([Num], [Counts], [Links], [Result], [Code]) VALUES (10, 1, 1, 1, N'E')
GO
A machine can have any different code.
I calculate my score using the code below, based on specified ratios ( 5% for code E machines, 95% for all other codes)
Select Num
,202012 as Yr_Month
,Sum(score) * 0.50 as score
From (
Select rs.Num
,Sum(Result) * 0.95 as score
,Sum(Links) as Link
From apps rs
Where rs.Code <> 'E'
Group By rs.Num
Union all
Select rs.Num
,Sum(Result) * 0.05 as score
,Sum(Links) as Link
From apps rs
Where Code = 'E'
Group By Num
) x
Group By Num
Result is the below:
Num Yr_Month score
3 202012 0.879344729344729
10 202012 0.025
My requirement is, if a machine ONLY has the E code (for example, in the table, machine Num = 10, only has code E), then the ratios will change - thus, the machine will have the complete 100% ratio
How can I update the above code to accommodate this requirement?
Desired result will be:
Num Yr_Month score
3 202012 0.879344729344729
10 202012 0.5
Please assist,
Thanks