CODEWITHSUNDEEP
sqlsql-servert-sql
Niyas
Niyas

Reputation: 5

Getting sum grouped by different fields in SQL Server

This is my table 

 PayerID PaymentID Amount

    1   8386    5827.14
    1   8386    5827.14
    1   8386    5827.14
    1   8387    4464.68
    1   8387    4464.68
    1   8387    4464.68
    2   8388    1482.57
    2   8388    1482.57
    2   8388    1482.57
    2   8388    1482.57
    2   8388    1482.57
    2   8388    1482.57
    2   8388    1482.57
    2   8388    1482.57
    2   8388    1482.57

I want Sum of Amount for distinct PaymentID group by PayerID like below

payerID AmountSum
1        5827.14 + 4464.68
2         1482.57

Upvotes: 1

Views: 53

Answers (2)

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13959

You can query using row_number as below:

Select PayerId, sum(amount) from (
    Select *, RowN = Row_Number() over(partition by Payerid, PaymentId, Amount order by Amount) from #payerdata
) a
where a.RowN = 1
group by PayerId

Output as below:

+---------+-----------+
| PayerId | AmountSum |
+---------+-----------+
|       1 |  10291.82 |
|       2 |   1482.57 |
+---------+-----------+

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175586

Use SUM(DISTINCT)

SELECT PayerId, SUM(DISTINCT Amount) AS AmountSum
FROM tab
GROUP BY PayerId;

This solution assumes that Amount is unique for different PaymentId.

More general approach:

SELECT PayerId, SUM(Amount) AS AmountSum
FROM (SELECT DISTINCT PayerID, PaymentID, Amount
      FROM tab) sub
GROUP BY PayerId

Upvotes: 4

Related Questions