Hi! I have two table with data in sql. But I want get below result.
stdId
|
lname
|
spacial
|
course
|
summoney
|
1
|
Asrori Yatim
|
15010202
|
1
|
1600
|
2
|
Pulodov Rustam
|
15010202
|
1
|
5150
|
3
|
Sharipov Sadriddin
|
15010202
|
1
|
NULL
|
5
|
Soliev Firuzjon
|
15010202
|
1
|
NULL
|
declare @tblStudents as table(stdId int, lname varchar(100), spacial varchar(10), course int, stdActive int)
insert into @tblStudents values(1, 'Pulodov Rustam', '15010202', 1, 1)
insert into @tblStudents values(2, 'Sharipov Sadriddin', '15010202', 1, 1)
insert into @tblStudents values(3, 'Soliev Firuzjon', '15010202', 1, 1)
insert into @tblStudents values(4, 'Malaev Nurullo', '15010202', 1, 0)
insert into @tblStudents values(5, 'Asrori Yatim', '15010202', 1, 1)
select * from @tblStudents
declare @tabEgrMoney as table(id int, studId int, SumMoney decimal(18, 0), moneyType int)
insert into @tabEgrMoney values(1, 1, '1500', 0)
insert into @tabEgrMoney values(2, 1, '3500', 0)
insert into @tabEgrMoney values(3, 1, '150', 1)
insert into @tabEgrMoney values(4, 5, '1200', 0)
insert into @tabEgrMoney values(5, 5, '350', 0)
insert into @tabEgrMoney values(6, 5, '50', 1)
select * from @tabEgrMoney
select stdId, lname, spacial, course, SUM(summoney)summoney from @tblStudents s full join @tabEgrMoney m on s.stdId = m.studId where stdActive = 1 and moneyType = 0 group by stdId, lname, spacial, course
But it's show below result:
stdId
|
lname
|
spacial
|
course
|
summoney
|
1
|
Asrori Yatim
|
15010202
|
1
|
1600
|
5
|
Pulodov Rustam
|
15010202
|
1
|
5150
|