How can I add a SUM clause for a certain variable to a WHERE statement in SQL Server

I am using SQL Server view design:

SELECT
    RaceDateTime, Meeting, Horse, Jockey, Trainer, CardNumber,
    Result, [WinSP ], Going, Furlongs, Prize, 
FROM
    dbo.[RACE-TEMPLATE]
WHERE
    (1 = 1) AND
    (BetfairSP_Rank = 1) AND
    (YEAR(RaceDateTime) > 2016) AND
    (Furlongs <= 8) AND
    (Jockey = 'Smith')

The above is a sample of the program which is run as is. I also want to add an additional output figure.

SUM [WinSP ] of all cases where 'Result' = 1

Then report the title as follows.

COUNT (TOTAL RECORDS) COUNT (Results = 1)  SUM ([WinSP ] of all cases where 'Result' = 1)

Solution

You can use a windowed conditional counts and sums. Windowing uses the OVER clause, in this case it seems you just want OVER ().

SELECT
    RaceDateTime, Meeting, Horse, Jockey, Trainer, CardNumber,
    Result, [WinSP ], Going, Furlongs, Prize, 
    TotalCount    = COUNT(*) OVER (),
    Results1Count = COUNT(CASE WHEN Results = 1 THEN 1 END) OVER (),
    Results1WinSP = SUM(CASE WHEN Results = 1 THEN WinSP END) OVER ()
FROM
    dbo.[RACE-TEMPLATE]
WHERE
    (1 = 1) AND
    (BetfairSP_Rank = 1) AND
    (RaceDateTime >= '20160101' AND RaceDateTime < '20170101') AND
    (Furlongs <= 8) AND
    (Jockey = 'Smith')
;

Note also the use of a proper date range, rather than using the YEAR function, as it means the server can properly utilize indexes.