Windows Dev Center

# Another method of using the Sum function

• ### 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

• 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?
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
```

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

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