1

I'm trying to find a method to sum a dollar amount in a small data set, but only on certain rows. This data is extracted from another system that I cannot change. This will be used for one-time data import into SQL so pretty and efficient are low priority. I need to sum the rows where a specific person ID has made two or more payments for the same event code.

In the data, all columns (many not shown) for the person paying are the same except for AmountPaid and DatePaid. I want to SUM AmountPaid (and get MAX of DatePaid if possible).

Data looks like this:

EventCode | PersonID | DatePaid | AmountPaid
****    
EventA | Person123 |2017-01-01 | $50
EventA | Person456 |2017-02-01 | $100
EventA | Person123 |2017-02-02 | $50
EventB | Person123 |2016-01-01 | $100
EventC | Person456 |2017-07-07 | $200
EventC | Person123 |2017-08-08 | $200

What I need to do is total just the payments for EventA made by Person123 - total should $100 (50+50), so I can import this as one payment transaction.

Ideal results would like this:

EventCode | PersonID | DatePaid | AmountPaid
****    
EventA | Person456 |2017-02-01 | $100
EventA | Person123 |2017-02-02 | $100
EventB | Person123 |2016-01-01 | $100
EventC | Person456 |2017-07-07 | $200
EventC | Person123 |2017-08-08 | $200

Thanks,

Area51Resident
  • 71
  • 1
  • 2
  • 8
  • So do you need to return all of the many more columns or no? It is best to include all the information necessary to give you a complete solution if you want a complete answer. – Jacob H Aug 29 '17 at 12:57
  • group by using personID – chirag satapara Aug 29 '17 at 12:59
  • Yes I need to return all columns. There are 132 columns in the table created from the extracted data, a bit long to try to include here. Every other column for 'Person123' and 'EventA' are identical except DatePaid and AmountPaid. There is no transaction identifier I can use to link the entries. – Area51Resident Aug 29 '17 at 13:05

2 Answers2

1

Try this, what does it return for you ?

Select
    SQ.ID
    ,SQ.EventCode
    ,SQ.PersonID
    ,SQ.Datepaid
    ,SUM(SQ.Amountpaid)
    ,MAX(SQ.Amountpaid)

    From
    (
    Select
    Cast(EventCode As Varchar(20)) +'-'+Cast(PersonID As Varchar(20)) +'-'+Cast(DatePaid As Varchar(20)) AS 'ID'
    ,EventCode
    ,PersonID
    ,Datepaid
    ,AmountPaid
    From [TableNAme]
    Group By
    Cast(EventCode As Varchar(20)) +'-'+Cast(PersonID As Varchar(20)) +'-'+Cast(DatePaid As Varchar(20)) 
    ,EventCode
    ,PersonID 
    ) As SQ

Group By
    SQ.ID
    ,SQ.EventCode
    ,SQ.PersonID
    ,SQ.Datepaid

To select ALL columns the below may work for you instead

Select
*
    ,SUM(SQ.Amountpaid)
    ,MAX(SQ.Amountpaid)

    From
    (
    Select *
    ,Cast(EventCode As Varchar(20)) +'-'+Cast(PersonID As Varchar(20)) +'-'+Cast(DatePaid As Varchar(20)) AS 'ID'
    From [TableNAme]
    Group By
    Cast(EventCode As Varchar(20)) +'-'+Cast(PersonID As Varchar(20)) +'-'+Cast(DatePaid As Varchar(20)) 
    ,EventCode
    ,PersonID 
    ) As SQ

Group By
    SQ.ID
    ,SQ.EventCode
    ,SQ.PersonID
    ,SQ.Datepaid
PeterH
  • 975
  • 2
  • 14
  • 36
  • Seems to be missing the DatePaid column. – Jacob H Aug 29 '17 at 13:03
  • i've amended it with a subquery, would that work now ? – PeterH Aug 29 '17 at 13:15
  • @User91504 I'm unable to get that to work - the upper example still returns two rows for Person123. Didn't get to far with the second as I have add 100+ columns to the GROUP BY aggregation to see if it will work. – Area51Resident Aug 29 '17 at 13:53
  • try removing the SQ.ID from the select, and then group by – PeterH Aug 29 '17 at 13:55
  • I think the issue with this approach is that I have add every column I need to GROUP BY in the inner SELECT - this doesn't aggregate the row(s) I need to SUM. Similar issue with the outer GROUP BY, to get every column I have list each column so it will see DatePaid and AmountPaid as discrete values and won't group them. The sample by @EricZ works so I'll go with that. Thanks for your help. – Area51Resident Aug 29 '17 at 16:31
1

If you have more columns in the table as you mentioned in the comment and it's one time tassk, you may try the following

SELECT   DISTINCT
  EventCode,
  PersonID,
  DatePaid = (SELECT MAX(DatePaid) FROM YourTable t2 WHERE t1.EventCode = t2.EventCode AND t1.PersonID = t2.PersonID) ,
  AmountPaid= (SELECT SUM(AmountPaid) FROM YourTable t2 WHERE t1.EventCode = t2.EventCode AND t1.PersonID = t2.PersonID)
  //select other 100+ columns
FROM YourTable t1
EricZ
  • 6,065
  • 1
  • 30
  • 30