( select 'G'
+ SUBSTRING(CONVERT(VARCHAR(100), CAST(wea AS DECIMAL (38, 2))), 3, 3) + '/'
+ SUBSTRING(CONVERT(VARCHAR(100), CAST(WEA AS DECIMAL (38,2))),6,3),
Base1 AS 'Primary',
AVG(ssi1) OVER (PARTITION BY wea,Base1, CONVERT(DATE,Date_time)) as Primary_SSI
FROM RT_Group_Status
) AS 'Group_ID',
You're trying to return three separate values in a single column. That's not going to work.
You need to separate the columns; and you don't need the sub-queries:
'G'
+ SUBSTRING(CONVERT(VARCHAR(100), CAST(wea AS DECIMAL (38, 2))), 3, 3) + '/'
+ SUBSTRING(CONVERT(VARCHAR(100), CAST(WEA AS DECIMAL (38,2))),6,3),
As 'Group_ID',
Base1 As 'Primary',
AVG(ssi1) OVER (PARTITION BY wea, Base1, CONVERT(date, Date_time)) As Primary_SSI