How to form query for view from ms sql database?

Question

SQL Server

There is such an ER-diagram DB.

It is necessary to create representations:
1) Type of sport, number of competitions per year, number of hits of athletes from 'Russia' in the prize-winners.
2) Country, number of athletes from that country, total number of victories by athletes from that country during the year in competitions that include the word 'swimming' in their names.
3) Kind of sport, records set in this kind of sport for the last five years, place of competitions where records are set.
There is a big difficulty with creating a selection with several fields for quantity at once. In 1) case came to this code:

SELECT kof.name_of_sport, COUNT(name_of_sport) as yearCount FROM RESULTS r
  JOIN KIND_OF_SPORT kof ON r.kind_of_sport_id = kof.id
  WHERE r.date_of_event >= '2017-01-01' AND r.date_of_event <= '2018-12-12'
  GROUP BY kof.name_of_sport) as T, RESULTS r

But I can't imagine how to further find the number of hits of athletes from 'Russia' in the prize-winners.
And in general, how to create one select with several count(...) at once. I would be grateful for help)

Answer 1

There is a big difficulty with creating a selection with several fields for quantity at once.

Calculation of each quantity by a separate subquery.

SELECT [спорт],
  (SELECT COUNT(*) FROM [результат] AS R1 WHERE R1.[спорт] = S.[спорт] ) AS [результат] 
  (SELECT COUNT(*) 
     FROM [результат] AS R2 JOIN [спортсмен] AS P ON P.[спортсмен] = R2.[спортсмен]
     WHERE R2.[спорт] = S.[спорт] AND P.[страна] = 'Россия' ) AS [результат Россия]
FROM [спорт] AS S

All other conditions add yourself.