question

You might want to have a look at the `HAVING` clause for your `GROUP BY` - think of `HAVING` as being a `WHERE` clause for the GROUP BY aggregates... http://msdn.microsoft.com/en-us/library/ms180199.aspx

The query already has the predicate restricting to consider only those records which have AimType = 2, so it looks like all that needs to happen is that the rows which are included in the group by clause simply need to have more than one distinct ProgrammeType. The real problem is that the ProgrammeType is already included in the group by column list, but this should not happen. As @ThomasRushton already suggested, you need to add a **HAVING** clause to your query after the **group by** but before the **order by** and also your original query (simply returning a single record for every Providor, Learner, Aim, AimType combination) with ProviderType removed needs to become the source of the final join with the original table. This will quarantee to return only those records which satisfy the predicate in the having clause: ;with records as ( select A.Providor, A.Learner, A.Aim, A.AimType from [SILR1213_AIMS_SN03] as A inner join ( select Providor, Learner from [SILR1213_LEARNER_SN03] group by Providor, Learner ) as B on B.Learner = A.Learner AND A.Providor = B.Providor where A.Aim = 30 and A.AimType = 2 and A.ProgrammeType in (1, 2) group by A.Providor, A.Learner, A.Aim, A.AimType having count(1) > 1 and count(distinct A.ProgrammeType) > 1 ) select A.Providor, A.Learner, A.Aim, A.AimType, A.ProgrammeType from [SILR1213_AIMS_SN03] as A inner join records on A.Providor = records.Providor and A.Learner = records.Learner and A.Aim = records.Aim and A.AimType = records.AimType order by Providor, Learner; The having clause will restrict the inner query to consider only those rows which have more than one record for the same AimType = 2 but different ProgrammeType values. Hope this helps, Oleg