Combined MSSQL for grouped computing data

Unit:

ID name
1. CNPC

2 China Mobile

3 China Telecom

Another table info:

Id unitid num point
1 1 1 2
2 1 2 1
3 2 1 1
4 3 2 2

If num = 1, get 1 point. If num = 2, get 3 points.
If point = 1, get 1 point. If point = 2, get 3 points.
In addition, the unit record in the info table scored 1 point.

The expected table is as follows:

Name score
CNPC 10
China Mobile 3
China Telecom 7

For example, the calculation of the first 10 points (in the info table unitid is 1, there are two + 2 points, and one num is 1 and the other is 2, so add 4 points, point is 2 and 1 is 1, SO 4 points are added, total 10 points)
)

 

Select A. Name, Sum ( Case When B. Num = 1 Then 1 When B. Num = 2 Then 3 Else 0 End )
+ Sum ( Case When B. Point = 1 Then 1 When B. Point = 2 Then 3 Else 0 End )
+ Sum ( Case When B. unitid Is Not Null Then 1 Else 0 End ) As Score
From Unit Left Join Info B
On B. unitid = A. ID
Group By A. Name

 

 

Left join *** on *** where

 

 

 

Application:

 

Select top 1 @ REX = Rex, @ Rey = Rey, @ rej = rej, @ Typex = Typex, @ typem = typem, @ typeo = typeo, @ stateq = stateq, @ statewes = statewes, @ casenum = casenum from t_assesspoint

If (@ time1 = NULL or @ time1 = '')
Select a. unitname, sum (case when B. casecheckid = 1 then @ Rex when B. casecheckid = 2 then @ Rey when B. casecheckid = 3 then @ rej else 0 end)
+ Sum (case when B. casetypeid = 1 then @ typem when B. casetypeid = 2 then @ Typex when B. casetypeid = 3 then @ typeo else 0 end)
+ Sum (case when B. casestatus = 1 then @ stateq when B. casestatus = 2 then @ statej else 0 end)
+ Sum (case when B. unitid is not null then @ casenum else 0 end) as number
From t_unit a left join t_case B
On B. unitid = A. ID where A. ID in (select * From DBO. splitstr (@ unitid ,','))
Group by A. unitname order by number DESC