We help IT Professionals succeed at work.

sum of recordset values using asp and SQL server

1,029 Views
Last Modified: 2011-08-18
I have this query which returns the correct records...

SELECT dbo.claim_line.*
FROM dbo.claim_line
WHERE [Claimant No_] = MMColParam2 AND [Benefit Code] = MMColParam AND [Start_Date] >= MMColParam3  AND [Start_Date] <= MMColParam4
ORDER BY Start_Date;

What I want to be able to do underneath is the total from a field in the claim_line table called amounttobepaid.  I currently bind the amounttobepaid column in the rersults list for each record, but underneath I need to total all the values for all records returned..  This field is formatted as currency..  Could I do another query or build it into this one?

I currently have tried the following SUM query, which is based on the above, just to get the total.. but it works for some perfectly, but others it brings back some crazy totals that do not match the records on show???

SELECT SUM (Amounttobepaid) Claim_Amounts
FROM dbo.claim_line
WHERE [Claimant No_] = MMColParam2 AND [Benefit Code] = MMColParam AND [Start_Date] >= MMColParam3  AND [Start_Date] <= MMColParam4
Comment
Watch Question

Commented:
SELECT SUM(Amounttobepaid) AS Claim_amounts, [ put you all other columns here]
FROM dbo.claim_line
WHERE [Claimant No_] = MMColParam2 AND [Benefit Code] = MMColParam AND [Start_Date] >= MMColParam3  AND [Start_Date] <= MMColParam4
GROUP BY [all other columns except the SUM column)

try this and see if you are getting what you want

Author

Commented:
hi k_rasuri,

Thanks for the reply..

it is giving me an error when I try to put any field names in the first square brackets e.g.  [Claimant No] after claim_amounts?? The query is at the bottom

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'FROM'.

--------------------------------

SELECT SUM(Amounttobepaid) AS Claim_amounts, [Claimant No],
FROM dbo.claim_line
WHERE [Claimant No_] = MMColParam2 AND [Benefit Code] = MMColParam AND [Start_Date] >= MMColParam3  AND [Start_Date] <= MMColParam4 GROUP BY [amounttobepaid]

Commented:
remove the comma at the end of the [Claimant No]
as there are no columns after that you dont need comma
apart from taht i dont see anything wrong...let me know

Commented:
sorry...there is one more problem..
you have to GROUP BY on [Claimant NO] , not on [amounttobepaid]

so the query looks like
SELECT SUM(Amounttobepaid) AS Claim_amounts, [Claimant No]
FROM dbo.claim_line
WHERE [Claimant No_] = MMColParam2 AND [Benefit Code] = MMColParam AND [Start_Date] >= MMColParam3  AND [Start_Date] <= MMColParam4 GROUP BY [Claimant NO]

Author

Commented:
it is giving this error??

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type nvarchar to float.
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.