By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table info (id int, name varchar(50), age int);
create table score (id int, score int, date varchar(50));
insert into info
values(1,'A',12),
(2,'B',14),
(3,'C',16);
insert into score
values(1,1,'16-06-19'),
(2,1,'06-06-18'),
(1,1,'15-06-19'),
(3,1,'30-06-19');
7 rows affected
select info.id, info.name,info.age, sum(isnull(score,0)) as monthS,sum(isnull(score,0)) as yearS
from info
left join
(
select * from score
where MONTH(convert(datetime,score.date,5)) = 6 and Year(convert(datetime,score.date,5)) = 2019
) as s
on info.id = s.id
group by info.id, info.name,info.age
| id | name | age | monthS | yearS |
|---|---|---|---|---|
| 1 | A | 12 | 2 | 2 |
| 2 | B | 14 | 0 | 0 |
| 3 | C | 16 | 1 | 1 |
select info.id, info.name,info.age, sum(case when MONTH(convert(datetime,score.date,5)) = 6 and Year(convert(datetime,score.date,5)) = 2019 then score else 0 end) as monthS,sum(case when MONTH(convert(datetime,score.date,5)) = 6 and Year(convert(datetime,score.date,5)) = 2019 then score else 0 end) as yearS
from info
inner join score
on info.id = score.id
group by info.id, info.name,info.age
| id | name | age | monthS | yearS |
|---|---|---|---|---|
| 1 | A | 12 | 2 | 2 |
| 2 | B | 14 | 0 | 0 |
| 3 | C | 16 | 1 | 1 |