total on the last row - 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 2005 Forums

Transact-SQL (2005)

total on the last row

Author

Topic

DMarmolejos
Yak Posting Veteran

65 Posts

Posted - 2008-07-28 : 15:13:37

Is there a way to display the SubTotal at the last row of a column that has numbers in it?

thanks, any help is appreciated

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-28 : 15:14:56

Sure, but you need to provide more information so that we can help you with a solution. Show us a data example for starters.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

DMarmolejos
Yak Posting Veteran

65 Posts

Posted - 2008-07-28 : 15:28:31

select SUM(total) from orders

Total
______
1
2
3

i want...

Total
1
2
3
__
6

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-28 : 15:31:18

You shouldn't be doing that type of thing in SQL Server. You should instead do this in your application code.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-07-28 : 15:36:55

But SQL Server does have ROLLUP and GROUPING, which you might want to look into. They're a bit clumsy, though, and you should follow Tara's advice.

Jim

DMarmolejos
Yak Posting Veteran

65 Posts

Posted - 2008-07-28 : 15:38:02

im using SAP with SQL Server...

when i try to do this:

select num1, num2, num3, num4, num5, num6, num7, num8, num9, SUM(total)
from orders
group by num1, num2, num3, num4, num5, num6, num7, num8, num9
WITH ROLLUP

i get an error saying "too many expressions in the GROUP BY clause. The maximum is 10 when using ROLLUP or CUBE"

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-07-28 : 15:55:10

I'm not a big fan of ROLLUP or GROUPING, but I have used them when the front-end people say they can't do it(i.e., incompetent or lazy). And if it says you have too many columns there's nothing else you can do, other than follow Tara's advice.

Jim

DMarmolejos
Yak Posting Veteran

65 Posts

Posted - 2008-07-28 : 16:33:29

thanks to all

so since i cant touch the application code..theres no way to do this?

when i use rollup, its giving me repeating rows..

DMarmolejos
Yak Posting Veteran

65 Posts

Posted - 2008-07-28 : 16:37:56

thanks to all

so since i cant touch the application code..theres no way to do this?

when i use rollup, its giving me repeating rows.. with this code:

select num1, num2, num3, num4, num5, num6, num7, num8, SUM(total)
from orders
group by num1, num2, num3, num4, num5, num6, num7, num8
WITH ROLLUP

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-07-28 : 16:41:43

You can add a row to the result with the grand total, or even return two result sets, but unless your presentation layer (report/app/web page/etc) is altered to handle and display the grand total properly (with a line, or in bold, or with a space before, etc) then it will not make any sense. Your presentation code MUST change if you want to add grand totals, this cannot be done 100% in SQL Server if unless you want a result like this:

Total
1
2
3
6

In which case, again, there is no way for users to know that 6 is a grand total of the above values, it looks just like another value.

Your report or web page or application or wherever you are displaying this output needs to be changed.

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

DMarmolejos
Yak Posting Veteran

65 Posts

Posted - 2008-07-30 : 15:19:50

then is there a way to have the total in another column?

Select T1.Price, SUM(T1.Price) as TOTAL
FROM ITM1
GROUP BY T1.Price

gives me the same values in both columns.. what i want is this:

Price Total
1 6
2 6
3 6

any help is greatly appreciated

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-30 : 15:22:54

Select Price, (SELECT SUM(Price) FROM ITM1) AS Total
FROM ITM1

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

DMarmolejos
Yak Posting Veteran

65 Posts

Posted - 2008-07-30 : 15:26:12

^ gracias Tara ;)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-31 : 05:03:50

or

Select Price, SUM(Price) OVER() AS Total
FROM ITM1

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources