SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 total on the last row
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

DMarmolejos
Yak Posting Veteran

65 Posts

Posted - 07/28/2008 :  15:13:37  Show Profile  Reply with Quote
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
36941 Posts

Posted - 07/28/2008 :  15:14:56  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

DMarmolejos
Yak Posting Veteran

65 Posts

Posted - 07/28/2008 :  15:28:31  Show Profile  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 07/28/2008 :  15:31:18  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 07/28/2008 :  15:36:55  Show Profile  Reply with Quote
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
Go to Top of Page

DMarmolejos
Yak Posting Veteran

65 Posts

Posted - 07/28/2008 :  15:38:02  Show Profile  Reply with Quote
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
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 07/28/2008 :  15:55:10  Show Profile  Reply with Quote
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
Go to Top of Page

DMarmolejos
Yak Posting Veteran

65 Posts

Posted - 07/28/2008 :  16:33:29  Show Profile  Reply with Quote
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
Go to Top of Page

DMarmolejos
Yak Posting Veteran

65 Posts

Posted - 07/28/2008 :  16:37:56  Show Profile  Reply with Quote
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
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 07/28/2008 :  16:41:43  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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
Go to Top of Page

DMarmolejos
Yak Posting Veteran

65 Posts

Posted - 07/30/2008 :  15:19:50  Show Profile  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 07/30/2008 :  15:22:54  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

DMarmolejos
Yak Posting Veteran

65 Posts

Posted - 07/30/2008 :  15:26:12  Show Profile  Reply with Quote
^ gracias Tara ;)
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 07/31/2008 :  05:03:50  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
or

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

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000