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.

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

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

Answer 6

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