Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Running Totals

Status
Not open for further replies.

IanWaterman

Programmer
Jun 26, 2002
3,511
GB
I have two datasets and the only way I can join them causes the first set to duplicate on date.

I need to sum the first set of data but only on change of date

eg
31/07/2012 207.31
31/07/2012 207.31
03/09/2012 177.31
03/09/2012 177.31
I want to see a total of 384.62

How do I do that please

Thank you

Ian
 
Use a sub query. In that subquery do a distinct or group by (whatever works better) then sum in the outer query. So something like this:

Select
sum(amount)
from (select distinct date, amount from table) Totals


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

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------
 
Or if a group buy is better for you.....

Select
sum(amount)
from (select date, avg(amount) as Amount from table group by date) Totals


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

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------
 
The report is built from two quite complex queries and is slow to run.

So I do not really want to add another subquery just for these totals.

Is it possible to do this in an expression or with a variable. My background is in Crystal and this would be quite simple using a Running Total, which evaluates on change of date. Can such an RT be replicated in Reporting Services?

Thank you

Ian
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top