Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

How to sum in SQL removing varchar Expand / Collapse
Author
Message
Posted Friday, May 3, 2013 12:08 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 2:19 AM
Points: 99, Visits: 191

Hi All,

I am trying to sum of amount (data type varchar) column but I am getting error

error converting data type varchar to float.

when I checked the data very carefully I found that there is an amount like C000214534. But when I am removing that varchar amount I am not getting error

select sum(CONVERT(float, ft.amount)) FROM filetransaction ft WHERE ft.fileid <>332 this query gives me the result.

is there any way in sql so that it can sum removing varchar? I do not want to update as everyday the data is inserted from the client's end.

How can I sum those data except the data where varchar type.

Please help!!
Post #1449056
Posted Friday, May 3, 2013 3:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:43 AM
Points: 6,890, Visits: 14,254
SELECT 
SUM(x.FloatAmount)
FROM filetransaction ft
CROSS APPLY (
SELECT FloatAmount = CASE
WHEN ISNUMERIC(ft.amount) = 1 THEN CAST(ft.amount AS FLOAT)
ELSE 0 END
) x
WHERE 1 = 1

NOTE: ISNUMERIC has limitations. Here's a good reference with workarounds.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1449089
Posted Friday, May 3, 2013 3:14 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, December 14, 2014 11:09 PM
Points: 1,962, Visits: 2,406
Chris solution will work :)


_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1449091
Posted Friday, May 3, 2013 3:15 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 12:24 PM
Points: 3,098, Visits: 3,231
Hi

SELECT sum(CONVERT(float, ft.amount)) 
FROM filetransaction ft
WHERE ft.fileid <>332 and ISNUMERIC(ft.amount)=1

Check this also http://www.sqlservercentral.com/articles/IsNumeric/71512/

Regards
IgorMi




Igor Micev,
SQL Server developer at Seavus
www.seavus.com
Post #1449092
Posted Friday, May 3, 2013 3:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:43 AM
Points: 6,890, Visits: 14,254
IgorMi (5/3/2013)
Hi

SELECT sum(CONVERT(float, ft.amount)) 
FROM filetransaction ft
WHERE ft.fileid <>332 and ISNUMERIC(ft.amount)=1

Check this also http://www.sqlservercentral.com/articles/IsNumeric/71512/

Regards
IgorMi


Which of those two filters will SQL Server apply first?


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1449095
Posted Friday, May 3, 2013 3:50 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 12:24 PM
Points: 3,098, Visits: 3,231
ChrisM@Work (5/3/2013)
IgorMi (5/3/2013)
Hi

SELECT sum(CONVERT(float, ft.amount)) 
FROM filetransaction ft
WHERE ft.fileid <>332 and ISNUMERIC(ft.amount)=1

Check this also http://www.sqlservercentral.com/articles/IsNumeric/71512/

Regards
IgorMi


Which of those two filters will SQL Server apply first?


Filters in queries having WHERE clause connected by AND operators are evaluated from left to right in the order they are written.




Igor Micev,
SQL Server developer at Seavus
www.seavus.com
Post #1449103
Posted Friday, May 3, 2013 3:52 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:43 AM
Points: 6,890, Visits: 14,254
IgorMi (5/3/2013)
ChrisM@Work (5/3/2013)
IgorMi (5/3/2013)
Hi

SELECT sum(CONVERT(float, ft.amount)) 
FROM filetransaction ft
WHERE ft.fileid <>332 and ISNUMERIC(ft.amount)=1

Check this also http://www.sqlservercentral.com/articles/IsNumeric/71512/

Regards
IgorMi


Which of those two filters will SQL Server apply first?


Queries having WHERE clause connected by AND operators are evaluated from left to right in the order they are written.


What is the source for this statement? It's completely wrong.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1449106
Posted Friday, May 3, 2013 3:56 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 12:24 PM
Points: 3,098, Visits: 3,231
ChrisM@Work (5/3/2013)
IgorMi (5/3/2013)
ChrisM@Work (5/3/2013)
IgorMi (5/3/2013)
Hi

SELECT sum(CONVERT(float, ft.amount)) 
FROM filetransaction ft
WHERE ft.fileid <>332 and ISNUMERIC(ft.amount)=1

Check this also http://www.sqlservercentral.com/articles/IsNumeric/71512/

Regards
IgorMi


Which of those two filters will SQL Server apply first?


Queries having WHERE clause connected by AND operators are evaluated from left to right in the order they are written.


What is the source for this statement? It's completely wrong.


Aham
I now see. It should be:

SELECT sum(CONVERT(float, ft.amount)) 
FROM filetransaction ft
WHERE ISNUMERIC(ft.amount)=1

I copied ft.fileid <>332 mistakenly.




Igor Micev,
SQL Server developer at Seavus
www.seavus.com
Post #1449109
Posted Friday, May 3, 2013 3:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:43 AM
Points: 6,890, Visits: 14,254
IgorMi (5/3/2013)
ChrisM@Work (5/3/2013)
IgorMi (5/3/2013)
ChrisM@Work (5/3/2013)
IgorMi (5/3/2013)
Hi

SELECT sum(CONVERT(float, ft.amount)) 
FROM filetransaction ft
WHERE ft.fileid <>332 and ISNUMERIC(ft.amount)=1

Check this also http://www.sqlservercentral.com/articles/IsNumeric/71512/

Regards
IgorMi


Which of those two filters will SQL Server apply first?


Queries having WHERE clause connected by AND operators are evaluated from left to right in the order they are written.


What is the source for this statement? It's completely wrong.


Aham
I now see. It should be:

SELECT sum(CONVERT(float, ft.amount)) 
FROM filetransaction ft
WHERE ISNUMERIC(ft.amount)=1

I copied ft.fileid <>332 mistakenly.


Do you want to include the rows where ft.amount cannot be converted to a numeric datatype?


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1449111
Posted Friday, May 3, 2013 4:01 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 12:24 PM
Points: 3,098, Visits: 3,231
ChrisM@Work (5/3/2013)
IgorMi (5/3/2013)
ChrisM@Work (5/3/2013)
IgorMi (5/3/2013)
ChrisM@Work (5/3/2013)
IgorMi (5/3/2013)
Hi

SELECT sum(CONVERT(float, ft.amount)) 
FROM filetransaction ft
WHERE ft.fileid <>332 and ISNUMERIC(ft.amount)=1

Check this also http://www.sqlservercentral.com/articles/IsNumeric/71512/

Regards
IgorMi


Which of those two filters will SQL Server apply first?


Queries having WHERE clause connected by AND operators are evaluated from left to right in the order they are written.


What is the source for this statement? It's completely wrong.


Aham
I now see. It should be:

SELECT sum(CONVERT(float, ft.amount)) 
FROM filetransaction ft
WHERE ISNUMERIC(ft.amount)=1

I copied ft.fileid <>332 mistakenly.


Do you want to include the rows where ft.amount cannot be converted to a numeric datatype?


No, but I posted "Check this also http://www.sqlservercentral.com/articles/IsNumeric/71512/"




Igor Micev,
SQL Server developer at Seavus
www.seavus.com
Post #1449112
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse