Sum Different Row Name in SQL Server

Jes Sie

I have this query below where I get the sum of the insurance type. How can I get the sum of Basic, Option 1, and Voluntary 2 and name it with Short-Term? Thanks for all the help.

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Amit Mohanty3y ago

Accepted answer

To get the sum of the insurance types "Basic," "Option 1," and "Voluntary 2" and name it as "Short-Term," you can use a CASE statement to categorize the different insurance types and then group by the new category.

SELECT 
    COUNT(MotorCINo) AS PolicyCount,
    SUM(a.TotalPremium) AS TotalPremium,
    CASE 
        WHEN a.InsuranceType IN ('Basic', 'Option 1', 'Voluntary 2') THEN 'Short-Term'
        ELSE a.InsuranceType
    END AS InsuranceCategory
FROM tblMotorInsurance_eCI a
GROUP BY InsuranceCategory;