SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table EachWayBetting
(
  SPFav int,
  FinishingPosition int
);

insert into EachWayBetting (SPFav, FinishingPosition) values
(1, 1),
(1, 2),
(1, 2),
(1, 9),
(2, 7),
(2, 8),
(2, 2),
(2, 1);

select * from EachWayBetting;


SPFav
FinishingPosition


1
1

1
2

1
2

1
9

2
7

2
8

2
2

2
1


-- OP attempt
SELECT SPFav,
       COUNT(CASE WHEN FinishingPosition = 1 THEN 1 ELSE 0 END) as WinsByFavouritism,
       COUNT(CASE WHEN FinishingPosition <= 6 THEN 1 ELSE 0 END) as PlaceByFavouritism
FROM EachWayBetting
GROUP BY SPFav
ORDER BY SPFav


SPFav
WinsByFavouritism
PlaceByFavouritism


1
4
4

2
4
4


-- possible solution
SELECT SPFav,
       COUNT(CASE WHEN FinishingPosition = 1 THEN 1 END) as WinsByFavouritism,
       COUNT(CASE WHEN FinishingPosition <= 6 THEN 1 END) as PlaceByFavouritism
FROM EachWayBetting
GROUP BY SPFav
ORDER BY SPFav


SPFav
WinsByFavouritism
PlaceByFavouritism


1
1
3

2
1
2


Warning: Null value is eliminated by an aggregate or other SET operation.