Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server ASP
|
SQL Server ASP Discussions about ASP programming with Microsoft's SQL Server. For more ASP forums, see the ASP forum category.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server ASP section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 4th, 2003, 01:51 PM
Authorized User
 
Join Date: Jun 2003
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via ICQ to nlicata
Default total columns in sql query

Thanks in advance for answers...

I asked in asp/database but cant wait..

I am pulling data from ms sql server - all goes well and the records are displayed in a table (actually output to an excel window in IE)... the records are displayed (nearly 30,000) but I need to now total a couple of the columns.. For example:

   Totals Price1= 50 Price2= 55 Price3= 60

               Price1 Price2 Price3
Customer A 10 11 12
Customer B 10 11 12
Customer C 10 11 12
Customer D 10 11 12
Customer E 10 11 12

This all goes well but I need a few fields across top of page to total columns. It is more complicated but if I can figure this part out I can get the rest. I thought I could do it in vbscript but am having hard time. Would it be better to create a variable in the sql select statement that totals the columns (and then I will perform some basic math on those variables to get %'s, etc.) I am newer to sql statements then I am to asp - so any help is appreciated....

Nick
 
Old August 4th, 2003, 06:33 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 215
Thanks: 0
Thanked 0 Times in 0 Posts
Default

You could use output parameters to a proc. eg

Code:
CREATE PROCEDURE MyProc
@Sum1 Int OUTPUT, @Sum2 Int OUTPUT, @Sum3 Int OUTPUT
AS

SET NOCOUNT ON

SELECT @Sum1 = SUM(Price1), @Sum2 = SUM(Price2), @Sum3 = SUM(Price3)
FROM SomeTable

SELECT Price1, Price2, Price3
FROM SomeTable
You need to use a command object to execute the proc and get the output parameters. Also there can be problems with accessing output parameters and recordsets. From memory you can only get the output parameters once the recordset is closed. It might be the other way around.

regards
David Cameron





Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL Query to Convert Columns into Rows Niaz Oracle 3 February 5th, 2010 07:08 AM
Total Query kbsudhir Access 1 February 16th, 2008 05:41 AM
SQL Query to convert Columns into Rows....again phix SQL Server 2000 11 November 11th, 2007 09:59 AM
Formatting sql query rows as columns with stack sastian PHP Databases 0 March 25th, 2005 04:51 AM
SQL Query to Convert Columns into Rows Niaz SQL Server 2000 2 April 20th, 2004 01:36 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.