| Author |
 Topic  |
|
|
DMarmolejos
Yak Posting Veteran
65 Posts |
 Posted - 07/28/2008 : 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
USA
35007 Posts |
Posted - 07/28/2008 : 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 - 07/28/2008 : 15:28:31
|
select SUM(total) from orders
Total
______
1
2
3
i want...
Total
1
2
3
__
6 |
Edited by - DMarmolejos on 07/28/2008 15:29:01 |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 07/28/2008 : 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 - 07/28/2008 : 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" |
Edited by - DMarmolejos on 07/28/2008 15:38:52 |
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 07/28/2008 : 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 - 07/28/2008 : 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.. |
Edited by - DMarmolejos on 07/28/2008 16:36:30 |
|
|
|
DMarmolejos
Yak Posting Veteran
65 Posts |
Posted - 07/28/2008 : 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
USA
7423 Posts |
Posted - 07/28/2008 : 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
|
Edited by - jsmith8858 on 07/28/2008 16:42:48 |
|
|
|
DMarmolejos
Yak Posting Veteran
65 Posts |
Posted - 07/30/2008 : 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 |
Edited by - DMarmolejos on 07/30/2008 15:20:55 |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
|
|
DMarmolejos
Yak Posting Veteran
65 Posts |
Posted - 07/30/2008 : 15:26:12
|
| ^ gracias Tara ;) |
|
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
Posted - 07/31/2008 : 05:03:50
|
or
Select Price, SUM(Price) OVER() AS Total
FROM ITM1
Madhivanan
Failing to plan is Planning to fail |
|
|
| |
Topic |
|
total on the last row
