August 7, 2009 at 1:29 pm
I have a dilemma that requires some expert guidance....I have a report which uses a stored procedure, but it's incorrectly calculating the total of a specific column.
I need it to show the Grand Total, not including columns A, B, and C.
It seems simple enough, but if I knew the correct formula in SQL, I wouldn't be on this site seeking guidance.
Here's the code I have at this time for reference...
-------------
CREATE Procedure sa_web_RTWreport
@starttime varchar(23), @endtime varchar(23), @machineID varchar(3)
AS
SELECT mx_timestamp, mach_id, mx_2, mx_3, j_id, grade_spec, run_num, wgt_amount, dbo.CVTRMS(mx_timestamp) as action_time
FROM j_audit
WHERE dbo.CVTRMS(mx_timestamp) >= @starttime AND dbo.CVTRMS(mx_timestamp) < @endtime AND
(mx_2= 'CONSUME' OR mx_2 = 'SLOFF' - mx_2 = 'UNCONSUME' OR mx_2 ='UNDOSL' OR mx_2 = 'UNTURB') AND
mach_id like @machineID + '%'
ORDER BY mach_id, j_id, mx_timestamp
Return
GO
-----------
Thanks in advance for your assistance!
August 7, 2009 at 3:11 pm
You are going to need to use a sum() function to get the total. It is impossible to tell from your post what column you want to total. The basic syntax would be something like this.
select field1, field2, sum(total) as GrandTotal
from MyTable
Group by field1, field2
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply