Running Totals help - 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

Running Totals help

Author

Topic

Variasol
Starting Member

3 Posts

Posted - 2011-08-10 : 15:16:49

I wrote this SQL statement that has a running total. I need all invoices to show for say company ABC, but only display the running total as shown at the bottom:

select i.invoiceNumber,convert(char(10),i.invoiceDate,101) as InvoiceDate,i.CustomerID,i.SubTotal,
(select sum(SubTotal) from Invoice
where CustomerID = i.CustomerID) as RunningTotal
from Invoice i

order by i.customerID

The wrong results look like this:

InvoiceNumber InvoiceDate CustomerID SubTotal Running Total

B3332Z 11/26/2010 ABC 124.05 4854.05

B3334Z 05/30/2011 ABC 4730.00 4854.05

See how company ABC has a running total which is correct.. I'd like the result to look like this:

InvoiceNumber InvoiceDate CustomerID SubTotal Running Total

B3332Z 11/26/2010 ABC 124.05

B3334Z 05/30/2011 ABC 4730.00 4854.05

and so on where the only amount that shows in RunningTotal is the SUM based on CustomerID and so on...

Any thoughts?

Derek

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-08-10 : 15:36:05

Change it like this. I have to tell you though, that calculating running totals is something that SQL is pretty poor at, so if you have lot of data, you may run into peformance problems. There are a little bit more efficient methods, but even they are nothing much to write home about.

SELECT
	i.invoiceNumber,
	CONVERT(CHAR(10), i.invoiceDate, 101) AS InvoiceDate,
	i.CustomerID,
	i.SubTotal,
	(
	    SELECT
	    	SUM(SubTotal)
	    FROM
	    	Invoice i2
	    WHERE
	    	i2.CustomerID = i.CustomerID
	    	AND i2.invoiceDate <= i.invoiceDate
	) AS RunningTotal
FROM
	Invoice i

Variasol
Starting Member

3 Posts

Posted - 2011-08-10 : 15:49:04

Thanks you for reply. That is kind of what I have. It's slightly different, but not exactly what I need. See below where the first running total value is blank. I only want the running total to show at the company's last line.

Also should be ordered by customerid, as I don't care about date.

I'm looking for:

InvoiceNumber InvoiceDate CustomerID SubTotal Running Total

B3332Z 11/26/2010 ABC 124.05

B3334Z 05/30/2011 ABC 4730.00 4854.05

Variasol
Starting Member

3 Posts

Posted - 2011-08-15 : 11:27:42

quote:
Originally posted by sunitabeck

Change it like this. I have to tell you though, that calculating running totals is something that SQL is pretty poor at, so if you have lot of data, you may run into peformance problems. There are a little bit more efficient methods, but even they are nothing much to write home about.
SELECT
i.invoiceNumber,
CONVERT(CHAR(10), i.invoiceDate, 101) AS InvoiceDate,
i.CustomerID,
i.SubTotal,
(
SELECT
SUM(SubTotal)
FROM
Invoice i2
WHERE
i2.CustomerID = i.CustomerID
AND i2.invoiceDate <= i.invoiceDate
) AS RunningTotal
FROM
Invoice i

Any further thoughts?

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-08-15 : 17:19:26

Can you provide sample data and show the desired result for that data

Cheers
MIK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources