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).

  • Filtering consecutive dates in SQL Server
  • How Do I Split a Delimited String in SQL Server Without Creating a Function?
  • How does node-mssql handle connection pooling?
  • How do I add a vote to my database in a form?
  • Microsoft.ReportingServices.Interfaces.dll missing for SSRS 2005
  • SQL query inner join tables, print to HTML <select> tag
  • 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,

  • Recommendations for supporting both Oracle and SQL Server in the same ASP.NET app with NHibernate
  • razor set value of variable created by .query() method
  • Reconciling compiled prepared statement with execs thereafter in a SQL Server profiling session
  • Get Relations of a Table
  • if exists update else insert
  • Correct way to set collation in temporary table column TSQL
  • 2 Solutions collect form web for “SQL to SUM certain rows only”

    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
    

    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
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.