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

  • Optimising a SELECT query that runs slow on Oracle which runs quickly on SQL Server
  • There is insufficient system memory in resource pool 'default' to run this query
  • Connection string for SQL Server 2008 R2 Versus for SQL Server Express
  • Sql server date Column format
  • get new SQL record ID
  • How to use SQL Server stored procedures in Microsoft PowerBI?
  • 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,

  • Getting General error: 4004 General SQL Server error:
  • RODBC ERROR: Could not SQLExecDirect 'CREATE TABLE … when doing sqlSave
  • MERGE - conditional “WHEN MATCHED THEN UPDATE”
  • pyodbc.connect() works, but not sqlalchemy.create_engine().connect()
  • How to backup SQL Server Agent jobs?
  • Is it better to log to file or database?
  • 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.