How do I combine rows into group sets?

Question

I need to find the average of a set of values for customers with and without a claim statement. I have it narrowed down to the data I want but I can't seem to combine some of the data like I want to.

SELECT Motor_policies.Numclaims, ROUND(AVG(HealthDependentsAdults + DependentsKids),2) AS Dependants

FROM Health_Policies

JOIN Customer

ON customer.HealthID = Health_Policies.HealthID

JOIN Motor_Policies

ON Motor_policies.MotorID = Customer.MotorID

GROUP BY Numclaims

This gives me 4 rows - number of claims from 0-3 what

I need is one row of 0 claims and one row of claims 1-3

I tried putting: WHERE Numclaims = >1 but it returned syntax error. (I'm very new so could be in the wrong place but confident it wasn't)

If anyone can help at all I'd really appreciate it!

Answer 1

Aggregate by a CASE expression which places zero claims and 1-3 claims in separate buckets

SELECT
    CASE WHEN Numclaims = 0 THEN 'no claims'
         WHEN Numclaims BETWEEN 1 AND 3 THEN '1-3 claims' END AS n_claims,
    ROUND(AVG(HealthDependentsAdults + DependentsKids), 2) AS Dependants
FROM Health_Policies hp
INNER JOIN Customer c
    ON c.HealthID = hp.HealthID
INNER JOIN Motor_Policies mp
    ON mp.MotorID = c.MotorID
GROUP BY n_claims;