CODEWITHSUNDEEP
sql-serversql-server-2014
Area51Resident
Area51Resident

Reputation: 91

SQL to SUM certain rows only

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,

Upvotes: 3

Views: 18921

Answers (2)

PeterH
PeterH

Reputation: 1040

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

Upvotes: 1

EricZ
EricZ

Reputation: 6205

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

Upvotes: 3

Related Questions