sql - 在子查询中分组

我有一个表AssignmentMaster，其中有以下各列的数据

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

现在我想找出有关userID的TotalNumberofAssignment，即结果应该是：

UserID  TotalAssignment  TotalAssignmentofRefundAmount  TotalPaidAmount TotalRefundAmount
1               3                    2                       40               8
2               1                    1                       30               7
3               4                    1                        75              15

如何在MSSQL中获得高于给定的结果。
您的任何帮助都会对我有很大帮助。

最佳答案

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

注意：

如果RefundAmount始终> = 0，则SIGN(RefundAmount)有效。

如果不是，请更改为

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