locked
Another method of using the Sum function RRS feed

  • Question

  • using sum function with difference methode.
    Hi
    I have a table with the fields Pay PersonId - Date - Pay and amounts to the following table:
    PersonId Date Pay
    PersonId         Date         Pay
    1         2011/01/01     500000
    1         2011/01/03      400000
    2         2011/03/01      510000
    2         2011/01/01     650000
    All the above information the user wants to collect payment in person to obtain a date range.
    for example , For example, the sum paid by each person on the date of the x1 and  X2.
    What do you think is the best code
    thanks.
    Saturday, July 23, 2011 6:40 PM

Answers

  • If you want to pass a dates range:

    select PersonID, SUM(Pay) as [Total Pay]
    from PayInfo
    WHERE [Date] between @StartDate and @EndDate
    GROUP BY PersonID
    



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Sunday, July 24, 2011 4:57 AM

All replies

  • Can you provide a sample output for your current data?
    Shailly - If this post answers your query, please click "Mark As Answer" or "Vote as Helpful".
    Saturday, July 23, 2011 6:51 PM
  • Is this what you want?

    DECLARE @Amount TABLE (PersonId INT,Date DATE,Pay INT)
    
    INSERT INTO @Amount
    SELECT 1,'2011/01/01',500000
    UNION ALL
    SELECT 1,'2011/01/03',400000
    UNION ALL
    SELECT 2,'2011/03/01',510000
    UNION ALL
    SELECT 2,'2011/01/01',650000
    
    SELECT PersonId,SUM(Pay) as Sum_Amount
    FROM @Amount
    GROUP BY PersonId
    



    Shailly - If this post answers your query, please click "Mark As Answer" or "Vote as Helpful".
    Saturday, July 23, 2011 6:59 PM
  • If you want to pass a dates range:

    select PersonID, SUM(Pay) as [Total Pay]
    from PayInfo
    WHERE [Date] between @StartDate and @EndDate
    GROUP BY PersonID
    



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Sunday, July 24, 2011 4:57 AM
  • Hello Sh2007, i guess that i understand your need

    i assume the following data in the table

    id          PersionId   Date       Pay
    ----------- ----------- ---------- -----------
    1           1           2011-01-01 1000
    2           1           2011-01-02 1000
    4           1           2011-01-03 1000
    5           1           2011-01-01 1500
    6           2           2011-01-01 2000
    7           2           2011-01-01 2000

     

    & by the following query

     

    SELECT PersionId,[Date],SUM(Pay) As Pay
    FROM PersionsPayment
    GROUP BY [Date],PersionId
    

    the result will be as the following:

    PersionId   Date       Pay
    ----------- ---------- -----------
    1           2011-01-01 2500
    1           2011-01-02 1000
    1           2011-01-03 1000
    2           2011-01-01 4000

     

    Hope that it's helpful

    Sunday, July 24, 2011 11:05 AM
  • Every SQL forum or newsgroup expects  that you will post DDL, sample data and clear specifications  which might might include output.  This is explained in the  FAQ section.   We can not read minds; what would we need to know to do your job for you?

    Unless you have just started writing SQL, you should know use ISO-8601 Standards for temporal data, avoid needless dialect, basic data modeling and use ISO-11179 Standards for data element names. Please tell if you can change the DDL, what version of what product you are using and other things.

    Would you like to try again with good Netiquette?

     


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL
    Monday, July 25, 2011 3:31 AM
  • Is this what you expect?

     

    SELECT

     

    PersonId,SUM(Pay) as

    SumAmount

    FROM

     

    YourTable

    WHERE

     

    [Date] >= '2011/01/01' and [Date] <=

    '2011/01/01'

    GROUP

     

    BY PersonId

    Output:

    PersonId SumAmount

    --------------------

    1            500000
    2            650000

    Regards,

    Velmurugan S (SQL Server DBA)


    velmurugan.s
    Monday, July 25, 2011 7:33 AM