Как получить итоговую сумму для каждой страховой страны и среднее значение для каждого мода

Question

Как я могу вернуть общий Премиум для каждой InsuredCounty и среднее значение для ExperienceMod, SchedureMod,TerritoryMod, EffectiveMod в одном запросе?

select * 
from (SELECT Insured,
             InsuredCounty,
             PolicyNumber,
             EffectiveDate,
             PolicyType,
             SUM(Premium) as Premium,
             ExperienceMod,
             ISNULL(ScheduleMod,0) as ScheduleMod,
             TerritoryMod,
             ISNULL(EffectiveMod,0) as EffectiveMod,
            ROW_NUMBER() OVER (PARTITION BY Insured,PolicyNumber,Premium, TransactionType ORDER BY PolicyType DESC) as rid 
      FROM ProductionReportMetrics
      WHERE EffectiveDate <= EOMONTH(GETDATE())
      AND CompanyLine = 'Arch Insurance Company' 
      AND Insured <>'Jerry''s Test' 
      AND TransactionType = 'Policy'
      GROUP BY  Insured,
                InsuredCounty,
                PolicyNumber,
                PolicyType,
                EffectiveDate,
                experienceMod,
                ScheduleMod,
                TerritoryMod,
                EffectiveMod, 
                Premium,
                EffectiveDate,
                TransactionType
      ) b 
where rid = 1 
ORDER BY  InsuredCounty, Premium desc, EffectiveDate

На данный момент результат выглядит так:

Но желаемый результат будет выглядеть так:

Answer 1

Как насчет этого?

WITH CTE AS
(
    SELECT 
    InsuredCounty   AS InsuredCountry 
    , Premium       AS premium
    , ExperienceMod AS ExperienceMod
    , SchedureMod   AS SchedureMod
    , TerritoryMod  AS TerritoryMod
    , EffectiveMod  AS EffectiveMod
    FROM ProductionReportMetrics
    WHERE EffectiveDate <= EOMONTH(GETDATE())
        AND CompanyLine = 'Arch Insurance Company' 
        AND Insured <>'Jerry''s Test' 
        AND TransactionType = 'Policy'
)
SELECT InsuredCountry
    , SUM(premium) AS premium
    , AVG(ExperienceMod) AS ExperienceMod 
    , AVG(SchedureMod)   AS SchedureMod 
    , AVG(TerritoryMod)  AS TerritoryMod 
    , AVG(EffectiveMod)  AS EffectiveMod 
FROM CTE
GROUP BY InsuredCountry
ORDER BY InsuredCounty, Premium desc, EffectiveDate;