Total SUM() - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Total SUM()

Author

Topic

gpeter
Starting Member

5 Posts

Posted - 2003-09-17 : 12:04:01

How can I rewrite the stored proc below to include a total of the curTransacAmnt?

txtname txtAccountNum NumberOfTrans curTransacAmnt
aaaa 12345 3 12.25
bbbb 23456 5 10.90
cccc 34567 10 345.12

My total for all curTransacAmnt should be = 368.27 and if I want to total the numberoftrans = 18.

SELECT dbo.Account.txtName, dbo.Transactions.txtAccountNum
,COUNT(dbo.Transactions.txtAccountNum) as NumberOfTrans
,SUM(dbo.Transactions.curTransacAmnt) as curTransacAmnt
FROM dbo.Transactions INNER JOIN
dbo.Account ON
dbo.Transactions.txtAccountNum = dbo.Account.txtAccountNum
WHERE (dbo.Transactions.dteCycleDate = CONVERT(DATETIME,
'5/7/2002', 102)) AND
(dbo.Transactions.txtCompanyNum = '213695700') AND
(dbo.Transactions.blnReviewStatus = 0 OR dbo.Transactions.blnReviewStatus IS NULL )
GROUP BY dbo.Account.txtName, dbo.Transactions.txtAccountNum
ORDER BY dbo.Account.txtName

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-17 : 12:13:52

[code]
SELECT
COUNT(*) as NumberOfTrans,
SUM(dbo.Transactions.curTransacAmnt) as curTransacAmnt
FROM
dbo.Transactions
WHERE
(dbo.Transactions.dteCycleDate = CONVERT(DATETIME, '5/7/2002', 102)) AND
(dbo.Transactions.txtCompanyNum = '213695700') AND
(dbo.Transactions.blnReviewStatus = 0 OR
dbo.Transactions.blnReviewStatus IS NULL )
[/code]

- Jeff

gpeter
Starting Member

5 Posts

Posted - 2003-09-17 : 12:19:02

Thanks, but I didn't explain myself clearly. You will notice that your stored proc is the same as what I already have.

The data I showed is returned from the stored proc I already have. What I want to do is to modify the stored proc to include a total of all the returned SUM(curTransacAmnt).

I hope I explained this well.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-17 : 12:31:25

in the same result set? or as another result? how should the total be indicated?

what tool are you using to display these results? (i.e., ASP, access, Crystal, Excel) ? usually displaying totals and things of that nature is done by the presentation layer, NOT the database layer.

- Jeff

gpeter
Starting Member

5 Posts

Posted - 2003-09-17 : 12:38:38

It doesn't matter if I get the total of all the SUM() in the same or a different resultset. You see, I want the total (of all the SUM()) shown before I display all the SUM(). I know it wouldn't be a problem if I display the total (of all the SUM()) after I've shown all the SUM() because I can add all the SUM() and display it as the total (of all the SUM()). (how' s this for a tongue twister?)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-17 : 13:02:03

What tool are you using to display this data?

- Jeff

gpeter
Starting Member

5 Posts

Posted - 2003-09-17 : 13:19:23

asp

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-17 : 13:26:18

then this is what you do in ASP:


dim Total
dim Count
open recordset
r.movefirst
do while not r.eof
  -- display your results
  Total = Total + r("CurTransactAmt")
  Count = Count + 1
  r.movenext
loop

response.write "the totals are " & total & " and " & Count

Do not make SQL do this work you -- it is ASP's job. SQL will need to make 2 passes through the data to display a grand-total, in ASP it requires a couple of local variables.

- Jeff

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-17 : 14:16:41

GPeter -- are you still with me? did that help/make sense?

- Jeff

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-09-17 : 15:19:16

It is not typically SQL's job as jsmith8858 has said, but it can be done.

Bring the total and count back as a field in the resultset:

SELECT	dbo.Account.txtName
	, dbo.Transactions.txtAccountNum
	, COUNT(dbo.Transactions.txtAccountNum) as NumberOfTrans
	, SUM(dbo.Transactions.curTransacAmnt) as curTransacAmnt
	, x.TotalTrans
	, x.TotalTransacAmnt	

FROM 	dbo.Transactions INNER JOIN dbo.Account
	  ON dbo.Transactions.txtAccountNum = dbo.Account.txtAccountNum
	CROSS JOIN
	(
	SELECT	COUNT(dbo.Transactions.txtAccountNum) as TotalTrans
		, SUM(dbo.Transactions.curTransacAmnt) as TotalTransacAmnt
	FROM 	dbo.Transactions INNER JOIN dbo.Account
		  ON dbo.Transactions.txtAccountNum = dbo.Account.txtAccountNum
	WHERE	(dbo.Transactions.dteCycleDate = CONVERT(DATETIME, '5/7/2002', 102))
	AND	(dbo.Transactions.txtCompanyNum = '213695700')
	AND	(dbo.Transactions.blnReviewStatus = 0 OR dbo.Transactions.blnReviewStatus IS NULL )
	) x

WHERE	(dbo.Transactions.dteCycleDate = CONVERT(DATETIME, '5/7/2002', 102))
AND	(dbo.Transactions.txtCompanyNum = '213695700')
AND	(dbo.Transactions.blnReviewStatus = 0 OR dbo.Transactions.blnReviewStatus IS NULL )

or probably less appealing bring the results back as the "last" row of the data based on your sort order but only if you can assure it being last.

SELECT	dbo.Account.txtName
	, dbo.Transactions.txtAccountNum
	, COUNT(dbo.Transactions.txtAccountNum) as NumberOfTrans
	, SUM(dbo.Transactions.curTransacAmnt) as curTransacAmnt
FROM 	dbo.Transactions INNER JOIN dbo.Account
	  ON dbo.Transactions.txtAccountNum = dbo.Account.txtAccountNum
WHERE	(dbo.Transactions.dteCycleDate = CONVERT(DATETIME, '5/7/2002', 102))
AND	(dbo.Transactions.txtCompanyNum = '213695700')
AND	(dbo.Transactions.blnReviewStatus = 0 OR dbo.Transactions.blnReviewStatus IS NULL )
GROUP BY dbo.Account.txtName, dbo.Transactions.txtAccountNum

UNION

SELECT	'zzzz'
	, 999999
	, COUNT(dbo.Transactions.txtAccountNum) as NumberOfTrans
	, SUM(dbo.Transactions.curTransacAmnt) as curTransacAmnt
FROM 	dbo.Transactions INNER JOIN dbo.Account
	  ON dbo.Transactions.txtAccountNum = dbo.Account.txtAccountNum
WHERE	(dbo.Transactions.dteCycleDate = CONVERT(DATETIME, '5/7/2002', 102))
AND	(dbo.Transactions.txtCompanyNum = '213695700')
AND	(dbo.Transactions.blnReviewStatus = 0 OR dbo.Transactions.blnReviewStatus IS NULL )

ORDER BY dbo.Account.txtName

As you can see it does process the result set twice in SQL server so performance could be an issue. However, producing the Count and Total in ASP makes a second pass through the result set using a loop.
Test and see which makes the most sense for your app.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-17 : 15:22:03

Can you explain this:

quote:

However, producing the Count and Total in ASP makes a second pass through the result set using a loop.

with an example? My example of using the variables made 1 pass through the results.

- Jeff

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-09-17 : 16:54:17

quote:
Can you explain this:

quote:
--------------------------------------------------------------------------------

However, producing the Count and Total in ASP makes a second pass through the result set using a loop.

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

with an example? My example of using the variables made 1 pass through the results.

I agree, however, there are two processes run on the data to retrieve the dataset and calculate the totals.

Your way: 1 in SQL server and 1 in ASP app
My way: 2 in SQL server

Just offering an alternative to see if it made sense in this particular application/business. And to show that SQL can perform some powerful functions and return them in a single resultset.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-17 : 16:57:16

just remember that having SQL server access a set of data twice is NOT the same as accumulating a variable in an ASP page.

It may take SQL Server 5 minutes to run a query and return results. Do it twice, it takes 10 minutes.

It takes milliseconds (literally) to accumulate a variable in ASP and then tack on the results as a total at the end. It adds no appreciable overhead to the time it takes to return results, no disk I/O, no network traffic, etc.

- Jeff

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-09-17 : 17:06:23

I have agreed with you in every post I made here, in fact in my solution I said:

quote:
As you can see it does process the result set twice in SQL server so performance could be an issue.

But I have made use of such constructions many times before when the performance was not a problem.

It helped me learn a great deal about TSQL to try multiple ways to a solution.

Just offering a suggestion to test.

gpeter
Starting Member

5 Posts

Posted - 2003-09-17 : 18:34:26

Thanks drymchaser, I like the second solution using UNION. But could you explain how exactly that works as it seems that the second select is almost the same as the first select statement. How can the second select stmt get all the SUM(curTransacAmnt)?

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-09-17 : 20:24:22

It selects single placeholders ('zzzz' and 999999) in the fields where you grouped by in the first part of the query and calculates the count() and sum() aggregates of the entire set that satisfies original WHERE clause and returns a single row of data.

Using your example:
"zzzz 999999 368.27 18"

Plan use of the placeholders, based on what you know about the data, so you can use the values and the ORDER BY clause to get this row to return as the last row of the recordset.

airjrdn
Starting Member

35 Posts

Posted - 2003-09-18 : 16:34:23

You could also check into with rollup.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources