How to fix a request?

Question

SQL Server

From workbench (Mysql) you need to transfer the query to sql server. The query shows the total amount of funds that trainers earned in a particular month by selling services in the form of individual lessons.

workbench:

select sotrudniki.fio_sot as 'ФИО сотрудника', search_may.cost_may as 'may', search_jun.cost_jun as 'june'
from sotrudniki inner join (select oplata_ind_uslug.kod_uslugy, oplata_ind_uslug.kod_poseshenya, oplata_ind_uslug.date_oplat, price_list.vidy_ind_zanatiy, sum(price_list.coast_ind_uslug) as 'cost_may', sotrudniki.fio_sot from sotrudniki inner join (oplata_ind_uslug inner join price_list on oplata_ind_uslug.kod_price_list = price_list.kod_price_list) on sotrudniki.kod_sot = oplata_ind_uslug.kod_sot where month(oplata_ind_uslug.date_oplat) = 05 group by sotrudniki.fio_sot) as search_may on sotrudniki.fio_sot = search_may.fio_sot
inner join
(select oplata_ind_uslug.kod_uslugy, oplata_ind_uslug.kod_poseshenya, oplata_ind_uslug.date_oplat, price_list.vidy_ind_zanatiy, sum(price_list.coast_ind_uslug) as 'cost_jun', sotrudniki.fio_sot from sotrudniki inner join (oplata_ind_uslug inner join price_list on oplata_ind_uslug.kod_price_list = price_list.kod_price_list) on sotrudniki.kod_sot = oplata_ind_uslug.kod_sot where month(oplata_ind_uslug.date_oplat) = 06 group by sotrudniki.fio_sot) as search_jun on sotrudniki.fio_sot = search_jun.fio_sot
group by sotrudniki.fio_sot;

SQL Server:

use sportclub
select sotrudniki.fio_sot as 'ФИО сотрудника', sum(search_may.cost_may) as 'may', sum(search_jun.cost_jun) as 'june'
from sotrudniki
inner join (select oplata_ind_uslug.kod_uslugy, oplata_ind_uslug.kod_poseshenya, oplata_ind_uslug.date_oplat, price_list.vidy_ind_zanatiy, price_list.coast_ind_uslug as 'cost_may', sotrudniki.fio_sot from sotrudniki
inner join (oplata_ind_uslug inner join price_list on oplata_ind_uslug.kod_price_list = price_list.kod_price_list) on sotrudniki.kod_sot = oplata_ind_uslug.kod_sot where month(oplata_ind_uslug.date_oplat) = 05 group by sotrudniki.fio_sot, oplata_ind_uslug.kod_uslugy, oplata_ind_uslug.kod_poseshenya, oplata_ind_uslug.date_oplat, price_list.vidy_ind_zanatiy, price_list.coast_ind_uslug) as search_may on sotrudniki.fio_sot = search_may.fio_sot

inner join (select oplata_ind_uslug.kod_uslugy, oplata_ind_uslug.kod_poseshenya, oplata_ind_uslug.date_oplat, price_list.vidy_ind_zanatiy, price_list.coast_ind_uslug as 'cost_jun', sotrudniki.fio_sot from sotrudniki
inner join (oplata_ind_uslug inner join price_list on oplata_ind_uslug.kod_price_list = price_list.kod_price_list) on sotrudniki.kod_sot = oplata_ind_uslug.kod_sot where month(oplata_ind_uslug.date_oplat) = 06 group by sotrudniki.fio_sot, oplata_ind_uslug.kod_uslugy, oplata_ind_uslug.kod_poseshenya, oplata_ind_uslug.date_oplat, price_list.vidy_ind_zanatiy, price_list.coast_ind_uslug) as search_jun on sotrudniki.fio_sot = search_jun.fio_sot
group by sotrudniki.fio_sot

Already when working with sql server, I partially corrected the query so that at least some information was displayed, but in the end the numbers are still not correct, for some reason the profit of one coach is multiplied by 3, and the other by 2.

Answer 1

Documentation .
Use

-- Aggregate Function Syntax    
SUM ( [ ALL | DISTINCT ] expression )

or

-- Analytic Function Syntax   
SUM ([ ALL ] expression) OVER ( [ partition_by_clause ] order_by_clause)

Answer 2

joins need to be rewritten.