June 22, 2011 at 8:55 pm
I have a script that I can produce something like this.... account number, last name and account balance...
123|Wilson|25.50
456|Hobbs|51.65
789|Smith|220.00
432|Lopez|110.15
I want to add a footer to the result on the very last line that would look like this (for the above resultset)...
123|Wilson|25.50
456|Hobbs|51.65
789|Smith|220.00
432|Lopez|110.15
4|407.30
The first number is the number of records. The second sums all the account balances. I've googled but I'm only seeing CLR, VBScript, DB2 etc examples.
In regards to obtaining the row count, I've read about using "Select @@ROWCOUNT" but it's not working when I try and export the data with my script.
TIA,
John
June 22, 2011 at 11:03 pm
u can try cube/roll up.. or here is something without that..
Assuming this would be the table structure..
create table #test (pid int,t_name varchar(10),amount float)
/*
Insert the test data....
*/
SELECT * FROM #TEST
UNION ALL
SELECT CAST((SELECT COUNT(PID) FROM #TEST) AS VARCHAR(10)),CAST((SELECT SUM(AMOUNT) FROM #TEST) AS VARCHAR(10)),''
June 23, 2011 at 7:14 am
Both WITH CUBE and WITH ROLLUP require that you have a GROUP BY clause, which I don't think that you want to use in this case. The COMPUTE clause should give you what you are looking for.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 23, 2011 at 7:52 am
Note that it's being removed in Denali:
(From the 2008 R2 BOL)
This feature will be removed in the next version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. Use ROLLUP instead. For more information, see GROUP BY (Transact-SQL).
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply