Count and GROUP values from a CASE expression

Question

Trying to count the total number of wins and total times a horse finished in the top 6 and GROUP by the order of favouritism in a horse racing market.

I am referencing this answer since it is looking for a similar outcome Count the occurrences of DISTINCT values

I have tried this expression but when executed, it returns the same number in both case columns

ie;

SPFav   WinsByFavouritism   PlaceByFavouritism
1       4143                4143
2       3963                3963
3       3853                3853

This is the code I am running - what is causing this?

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 [NRaceHistory].[dbo].[EachWayBetting]

GROUP BY SPFav 

ORDER BY SPFav ;

I don't see a `DISTINCT` in your attempt, however, you only have 2 different `DISTINCT` values from your `CASE` expression, `1` and `0`. Sample data and expected results will help us help you here. Perhaos you should be returning the horse's name for the `THEN` and npt have an `ELSE`? Complete blind guess though. — Thom A, Sep 30 '20 at 09:50
Yes, but it is not the horse name I am interested in, I want to count how many times the SPFav 1,2,3 etc win races — Dallas, Sep 30 '20 at 10:00
So what *is* the `DISTINCT` value you want to count? Then just do what I said above, but use that instead of Horse Name. Like I said, it was a complete blind guess as we have no sample data or expected results here. — Thom A, Sep 30 '20 at 10:02

score 1 · Accepted Answer · answered Sep 30 '20 at 10:05

Working with the first comment this would give the following possible solution.

Sample data

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);

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

Result

SPFav WinsByFavouritism PlaceByFavouritism
----- ----------------- ------------------
1     1                 3
2     1                 2

Fiddle

That's right, using `count` is counting the `0` in the case expression - removing else (or changing `count` to `sum`) returns the correct answer — Dallas, Sep 30 '20 at 10:16

score 1 · Answer 2 · answered Sep 30 '20 at 10:11

I believe you should replace COUNT in your query with SUM. i.e. it should be:

SELECT SPFav, 
       SUM(CASE WHEN FinishingPosition = 1 THEN 1 ELSE 0 END) as WinsByFavouritism,
       SUM(CASE WHEN FinishingPosition <= 6 THEN 1 ELSE 0 END) as PlaceByFavouritism
FROM [NRaceHistory].[dbo].[EachWayBetting]
GROUP BY SPFav 
ORDER BY SPFav;

Count and GROUP values from a CASE expression

2 Answers2