Group by in Subquery

I have a table AssignmentMaster in that I have following columns with data

AssignmentID         PaidAmount     RefundAmount   UserID
1                     20               0               1
2                     10               5               1
3                     30               7               2
4                     25               0               3
5                     35               15              3
6                     10               3               1
7                     5                0               3
8                     10               0               3

Now I want to find out the TotalNumberofAssignment with respect to userID, i.e. result should be:

UserID  TotalAssignment  TotalAssignmentofRefundAmou开发者_开发百科nt  TotalPaidAmount TotalRefundAmount
1               3                    2                       40               8
2               1                    1                       30               7
3               4                    1                        75              15

How I can get above given result in MSSQL. your any help will help me lot.

---

SELECT
   UserID,
   COUNT(AssignmentID) AS TotalAssignment,
   SUM(SIGN(RefundAmount)) AS TotalAssignmentofRefundAmount,
   SUM(PaidAmount) AS TotalPaidAmount,
   SUM(RefundAmount) AS TotalRefundAmount
FROM
   MyTable
GROUP BY
   UserID

Note:

SIGN(RefundAmount) works if RefundAmount is always >= 0.

If not, change to

SUM(CASE WHEN RefundAmount <> 0 THEN 1 ELSE 0 END) AS TotalAssignmentofRefundAmount

---

Select UserID, 
       count(1) as TotalAssignment,
       sum( case when RefundAmount = 0 then 0 else 1 end) as TotalAssignmentofRefundAmount,  
       sum(PaidAmount) as TotalPaidAmount ,
       sum(RefundAmount) as TotalRefundAmount
From AssignmentMaster 
Group by UserID

---

To show how to do this using nested SQL:

Select  UserTotals.UserID, UserTotals.TotalAssignment,
        Refunds.TotalAssignmentofRefundAmount, 
        UserTotals.TotalPaidAmount, UserTotals.TotalRefundAmount
From    (select     UserID, 
                    Count(AssignmentID) [TotalAssignment],
                    Sum(PaidAmount)     [TotalPaidAmount],
                    sum(RefundAmount)   [TotalRefundAmount]
                    From        @AssignmentMaster
                    Group By    UserID
        ) [UserTotals] Left Join
        (Select     UserID,
                    Count(AssignmentID) [TotalAssignmentofRefundAmount]
                    From        @AssignmentMaster
                    Where       RefundAmount > 0 
                    Group By    UserID
        ) [Refunds] On Refunds.UserID = UserTotals.UserID

---

select UserId, count (AssignmentID) as TotalAssignment,
sum(case when RefundAmount = 0 then 0 else 1 end) as TotalAssignmentofRefundAmount,
sum(PaidAmount) as TotalPaidAmound,
sum(RefundAmount) as TotalRefundAmount
from AssignmentMaster
group by UserID;