sum records by date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

sum records by date

Author

Topic

sign_seventh
Yak Posting Veteran

66 Posts

Posted - 2008-04-09 : 23:38:22

this is my sample records in my table


Sdate        Amount
January 2007     250000.00
March 2007      300000.00
January 2008     350000.00

how can i get the amount from January to
May 2007 and sum it up like this


Details
January 2007     250000.00
February 2007   250000.00
March 2007     300000.00
April 2007     300000.00
May 2007     300000.00

this is all that i want to get


Total Amount: 1,400,000.00

pravin14u
Posting Yak Master

246 Posts

Posted - 2008-04-09 : 23:51:23

Select Sdate,Amount from TableName
UNION
Select 'Total Amount',sum(Amount) from TableName

Prakash.P
The secret to creativity is knowing how to hide your sources!

sign_seventh
Yak Posting Veteran

66 Posts

Posted - 2008-04-10 : 00:56:09

this will only gave me the total of all amount.

quote:
Originally posted by pravin14u

Select Sdate,Amount from TableName
UNION
Select 'Total Amount',sum(Amount) from TableName

Prakash.P
The secret to creativity is knowing how to hide your sources!

pravin14u
Posting Yak Master

246 Posts

Posted - 2008-04-10 : 01:02:03

quote:
Originally posted by sign_seventh

this will only gave me the total of all amount.

quote:
Originally posted by pravin14u

Select Sdate,Amount from TableName
UNION
Select 'Total Amount',sum(Amount) from TableName

Prakash.P
The secret to creativity is knowing how to hide your sources!

Select Sdate,Amount from TableName
UNION
Select 'Total Amount,sum(Amount) from TableName
where SDate in('January 2007','February 2007','March 2007','April2007','May 2007')

Prakash.P
The secret to creativity is knowing how to hide your sources!

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-04-10 : 01:04:05

where did these rows come from in your first result set:

February 2007 250000.00
April 2007 300000.00
May 2007 300000.00

are they supposed to be inferred based on the most recent month that actually has data in your sdate/amt table?

elsasoft.org

sign_seventh
Yak Posting Veteran

66 Posts

Posted - 2008-04-10 : 02:05:00

the original content of that table is actually this but to make it short i only choose the quo_user_id = 23

quo_id   | quo_datetime|   quo_amount|   quo_user_id
7   | 2007-07-01  |500000.00  |20
41  | 2008-01-01  |550000.00  |20
44  | 2008-01-01  |350000.00  |23
11  | 2007-01-01  |250000.00  |23
12  | 2007-03-01  |300000.00  |23
42  | 2008-01-01  |300000.00  |27
14  | 2007-01-01  |250000.00  |28
15  | 2007-03-01  |300000.00  |28

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-04-10 : 02:30:57

so what's wrong with pravin14u's answer then?

elsasoft.org

sign_seventh
Yak Posting Veteran

66 Posts

Posted - 2008-04-10 : 03:13:23

theres nothing wrong in his code, i actually tried it.i just want a dynamic one. like what if i want to get amount fr febuary '07 to march '08. i dnt want to put from feb 07 up to march 08 in my 'in clause'.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-04-10 : 03:22:20

Select Sdate,Amount from TableName
where SDate between @startDate and @endDate
UNION ALL
Select 'Total Amount,sum(Amount) from TableName
where SDate between @startDate and @endDate

elsasoft.org

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-10 : 05:30:47

What is wrong with GROUP BY and ROLLUP?

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources