Total at bottom of resultset - 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

Transact-SQL (2000)

Total at bottom of resultset

Author

Topic

superhero
Yak Posting Veteran

52 Posts

Posted - 2007-07-27 : 05:12:53

Hi everyone

Is it at all possible to get an additional row at the end of your SQL Query result with totals for the data being displayed?

For instance, if I had:

SELECT users.fullname AS IndexerName, COUNT(*) AS TotalItemsIndexed, COUNT(DISTINCT indexdata.containerid) AS TotalContainers
from indexdata inner join users on indexdata.userid = users.userid
group by users.fullname

So let's say I get 3 results back, as there's 3 users in the database. How would I then get an additional 4th row and make it say "TOTALS" instead of users.fullname and then add up TotalItemsIndxed and give a total, and add up TotalContainers and give a total - all in the 4th row called TOTALS?

Is this possible in SQL, or should I rather add it up in a datagrid in VB2005?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-27 : 05:24:47

SELECT users.fullname AS IndexerName, COUNT(*) AS TotalItemsIndexed, COUNT(DISTINCT indexdata.containerid) AS TotalContainers
from indexdata inner join users on indexdata.userid = users.userid
group by users.fullname

union all

SELECT 'Totals', COUNT(*) AS TotalItemsIndexed, COUNT(DISTINCT indexdata.containerid) AS TotalContainers
from indexdata inner join users on indexdata.userid = users.userid

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

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-07-27 : 05:50:11

Maybe add a constant column so that total is at the bottom of the result set.

SELECT SorOrder = 1, columns....
UNION
SELECT SortOrder = 2, totals from Peso's solution
ORDER BY SortOrder, your other order requirements

superhero
Yak Posting Veteran

52 Posts

Posted - 2007-07-27 : 05:52:33

Thanks Peso. Your code worked perfectly. I had a order by fullname which I had to change to order by TotalItemsIndexed asc so that the TOTALS will be at the bottom, otherwise it ordered it so that totals was somewhere between the names.

Thanks a lot.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-07-27 : 08:52:20

Typically, it is much more efficient and much easy and more flexible to display grand totals at your presentation layer, in the Grid. By doing it in T-SQL, not only your SQL code longer and harder to read, it is also twice as slow. Accumulating a total to display in datagrid is a simple task that takes very few CPU cycles, and ultimately you have much greater control how to display those totals (different colors, fonts, formatting, etc).

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources