SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to sum in SQL removing varchar


How to sum in SQL removing varchar

Author
Message
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16172 Visits: 19543
ChrisM@Work (5/3/2013)
IgorMi (5/3/2013)
<<snip>>
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
niladri.primalink
niladri.primalink
SSC-Enthusiastic
SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)

Group: General Forum Members
Points: 151 Visits: 226
Thank u all for your awesome output.

It works fine for me. But the same query when I am pasting in SP it shows me the error

SELECT file_id as bacs_id,

CASE WHEN EXISTS(SELECT 1 FROM filetransaction WHERE fileid = fb.file_id)
THEN
(SELECT sum(CONVERT(float, ft.amount))
FROM filetransaction ft
WHERE ft.fileid <>332 and ISNUMERIC(ft.amount)=1)


it shows me the error

Msg 8114, Level 16, State 5, Procedure bacs_list, Line 17
Error converting data type varchar to float.

but when I am removing CASE WHEN EXISTS(SELECT 1 FROM filetransaction WHERE fileid = fb.file_id) this portion it is giving me the output. I can not remove exists function

Could u please guide me?
Igor Micev
Igor Micev
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5882 Visits: 5080
ChrisM@Work (5/3/2013)
ChrisM@Work (5/3/2013)
IgorMi (5/3/2013)
<<snip>>
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.


Hi
I admit the source was not good. I examined the issue in more details and yours code
does not works. It reports an converting type of error

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




This works.
SELECT sum(convert(float,ft.amount)) 
FROM filetransaction ft
WHERE ISNUMERIC(rtrim(ltrim(ft.amount)))=1 and ft.amount not like '%['','',''$'']%'



I used http://www.sqlservercentral.com/articles/IsNumeric/71512/

Regards

Igor Micev,
SQL Server developer at Seavus
My blog: www.igormicev.com
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16172 Visits: 19543
niladri.primalink (5/3/2013)
Thank u all for your awesome output.

It works fine for me. But the same query when I am pasting in SP it shows me the error

SELECT file_id as bacs_id,

CASE WHEN EXISTS(SELECT 1 FROM filetransaction WHERE fileid = fb.file_id)
THEN
(SELECT sum(CONVERT(float, ft.amount))
FROM filetransaction ft
WHERE ft.fileid <>332 and ISNUMERIC(ft.amount)=1)


it shows me the error

Msg 8114, Level 16, State 5, Procedure bacs_list, Line 17
Error converting data type varchar to float.

but when I am removing CASE WHEN EXISTS(SELECT 1 FROM filetransaction WHERE fileid = fb.file_id) this portion it is giving me the output. I can not remove exists function

Could u please guide me?


Can you post the whole query please?

“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
niladri.primalink
niladri.primalink
SSC-Enthusiastic
SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)

Group: General Forum Members
Points: 151 Visits: 226
ya sure

USE [Kingethelbert]
GO
/****** Object: StoredProcedure [dbo].[bacs_list] Script Date: 05/03/2013 11:00:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- EXEC bacs_list
ALTER PROCEDURE [dbo].[bacs_list]
@company_id INT,
@status INT,
@INT_USERID INT = 0
AS
BEGIN

SET NOCOUNT ON;

DECLARE @INT_VIEW_OWN_FILE_ONLY INT = 0
SELECT @INT_VIEW_OWN_FILE_ONLY = view_own_file_only FROM [USER] WHERE USER_ID = @INT_USERID

-- *********** Added status column **********-------
IF (@INT_VIEW_OWN_FILE_ONLY = 0)
BEGIN
SELECT file_id as bacs_id,
listname NAME,
filename ,
filetype,
ISNULL((SELECT USER_NAME FROM userlogin WHERE user_id=createdby),'')created_by,
createdby,
validatestatus Modulas_Valid,
CONVERT(VARCHAR(15), createddate, 103) create_date,
CONVERT(DATE,createddate,103) AS Createdt,
(SELECT TOP 1 ISNULL(status,0)status FROM submission WHERE fileid=file_id AND file_type=1)sub_status

,CASE WHEN EXISTS(SELECT 1 FROM filetransaction WHERE fileid = fb.file_id)
THEN
(SELECT sum(CONVERT(float, ft.amount))
FROM filetransaction ft
WHERE ft.fileid <>332 and ISNUMERIC(ft.amount)=1)
--CAST((SELECT COALESCE(SUM(CONVERT(float, ft.amount))/100.00,0.00) FROM filetransaction ft
--WHERE ft.fileid=fb.file_id AND ft.iscontra=0 AND ft.transactioncode IN ('99', 'Z4', 'Z5') AND ISNUMERIC(ft.amount ) = 1) AS DECIMAL(13,2))
ELSE
CAST((SELECT COALESCE(SUM(CONVERT(float, FPSTemp.amount))/100.00,0.00) FROM filetransaction_excel FPSTemp INNER JOIN m_transactiontype mt ON mt.name = FPSTemp.transaction_code
WHERE FPSTemp.fileid= fb.file_id AND ISNUMERIC(FPSTemp.amount + '.0e0') = 1 AND FPSTemp.amount IS NOT NULL AND mt.transaction_code IN ('99', 'Z4', 'Z5')) AS DECIMAL(13,2)) END totcreditamount

,CASE WHEN EXISTS(SELECT 1 FROM filetransaction WHERE fileid = fb.file_id)
THEN
CAST((SELECT COALESCE(SUM(CONVERT(float, ft.amount))/100.00,0.00) FROM filetransaction ft
WHERE ft.fileid=fb.file_id AND ft.iscontra=0 AND ft.transactioncode IN ('01', '17', '18', '19') AND ISNUMERIC(ft.amount + '.0e0') = 1) AS DECIMAL(13,2))
ELSE
CAST((SELECT COALESCE(SUM(CONVERT(float, FPSTemp.amount))/100.00,0.00) FROM filetransaction_excel FPSTemp INNER JOIN m_transactiontype mt ON mt.name = FPSTemp.transaction_code WHERE FPSTemp.fileid= fb.file_id
AND ISNUMERIC(FPSTemp.amount + '.0e0') = 1 AND FPSTemp.amount IS NOT NULL AND mt.transaction_code IN ('01', '17', '18', '19')) AS DECIMAL(13,2)) END totdebitamount
FROM filebasic fb WHERE company_id=@company_id AND status=0
ORDER BY file_id DESC
END
ELSE
BEGIN
SELECT file_id AS bacs_id,
listname NAME,
filename ,
filetype,
ISNULL((SELECT USER_NAME FROM userlogin WHERE user_id=createdby),'')created_by,
createdby,
validatestatus Modulas_Valid,
CONVERT(VARCHAR(15), createddate, 103) create_date,
CONVERT(DATE,createddate,103) AS Createdt,
(SELECT TOP 1 ISNULL(status,0)status FROM submission WHERE fileid=file_id AND file_type=1)sub_status
,CASE WHEN EXISTS(SELECT 1 FROM filetransaction WHERE fileid = fb.file_id)
THEN CAST((SELECT SUM(CONVERT(FLOAT, ft.amount))/100.00 FROM filetransaction ft
WHERE ft.fileid=fb.file_id AND ft.iscontra=0 AND ft.transactioncode IN ('99', 'Z4', 'Z5') AND ISNUMERIC(ft.amount + '.0e0') = 1) AS DECIMAL(13,2))
ELSE
(SELECT ROUND(SUM(CONVERT(BIGINT, FPSTemp.amount))/100,2) FROM filetransaction_excel FPSTemp INNER JOIN m_transactiontype mt ON mt.name = FPSTemp.transaction_code
WHERE FPSTemp.fileid= fb.file_id AND ISNUMERIC(FPSTemp.amount + '.0e0') = 1 AND FPSTemp.amount IS NOT NULL AND mt.transaction_code IN ('99', 'Z4', 'Z5')) END totcreditamount

,CASE WHEN EXISTS(SELECT 1 FROM filetransaction WHERE fileid = fb.file_id)
THEN CAST((SELECT SUM(CONVERT(FLOAT, ft.amount))/100.00 FROM filetransaction ft
WHERE ft.fileid=fb.file_id AND ft.iscontra=0 AND ft.transactioncode IN ('01', '17', '18', '19') AND ISNUMERIC(ft.amount + '.0e0') = 1) AS DECIMAL(13,2))
ELSE
CAST((SELECT SUM(CONVERT(BIGINT, FPSTemp.amount))/100.00 FROM filetransaction_excel FPSTemp INNER JOIN m_transactiontype mt ON mt.name = FPSTemp.transaction_code WHERE FPSTemp.fileid= fb.file_id
AND ISNUMERIC(FPSTemp.amount + '.0e0') = 1 AND FPSTemp.amount IS NOT NULL AND mt.transaction_code IN ('01', '17', '18', '19')) AS DECIMAL(13,2)) END totdebitamount
FROM filebasic fb WHERE company_id=@company_id AND status=0 AND createdby = @INT_USERID
ORDER BY file_id DESC
END
END


this is an SP. and when I ran the sp like

exec bacs_list 1,0,175 then it shows me the error.


Please help!
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85573 Visits: 41082
My biggest concern is the conversion to FLOAT to do the sum. FLOAT only has a precision of 15 significant digits. If you go any higher than that, you will start to get siginficant rounding errors without any warning.

Use the appropriate datatype for these types of things. For most sums, FLOAT isn't it.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Sergiy
Sergiy
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10304 Visits: 11960
Jeff Moden (5/3/2013)
My biggest concern is the conversion to FLOAT to do the sum. FLOAT only has a precision of 15 significant digits. If you go any higher than that, you will start to get siginficant rounding errors without any warning.

Use the appropriate datatype for these types of things. For most sums, FLOAT isn't it.

Is it?
Really?

How about this one:

SELECT ROUND(SUM(CONVERT(BIGINT, FPSTemp.amount))/100,2)
?
Would make a good QoD: How many errors can you find in this piece of code?
Hehe
Sergiy
Sergiy
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10304 Visits: 11960
niladri.primalink,

this one does not look right:

   (SELECT TOP 1 ISNULL(status,0)status FROM submission WHERE fileid=file_id AND file_type=1)sub_status


TOP 1 with no ORDER BY means "random".
If there are 2 or more statuses for any particular file_id the result might be different every single time it's executed.

What do you need from this query?
Sergiy
Sergiy
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10304 Visits: 11960
Hi niladri.primalink,

Using correlated subqueries (especially so many of them) is as bad as having an open bottle of rum on the front seat of your car or carrying a gun openly on a street.
Lack of enforcement from the police (probably because they can't read SQL very well) does not make it less criminal.
:-D

I've tried to fix you query by moving subqueries to derived tables.
Some might suggest CTE - it's up to you which style to choose.
CTE might be the perfect choice for selecting "submission status" - if you can tell which one you need to select for each particular file_id.

I did not have table definitions and test data to test the script, so please do this part yourself - there might be some errors.

But anyway this might give you an idea how it should be done:

SELECT fb.{/*list pf columns here*/} 
ISNULL(UL.USER_NAME,'')created_by,
ISNULL (ft.totcreditamount, FTE.totcreditamount) totcreditamount,
ISNULL (ft.totdebitamount, FTE.totdebitamount) totdebitamount,

FROM filebasic fb
LEFT JOIN userlogin UL ON UL.user_id=fb.createdby
LEFT JOIN (
SELECT fileid, MAX(status))status
FROM submission
WHERE file_type=1
GROUP BY fileid )sub_status ON fileid = file_id
LEFT JOIN (
SELECT fileid,
CONVERT (DECIMAL(13,2), SUM(CONVERT(FLOAT, CASE WHEN transactioncode IN ('99', 'Z4', 'Z5') THEN amount ELSE 0.00 END) )/100.00 ) totcreditamount,
CONVERT (DECIMAL(13,2), SUM(CONVERT(FLOAT, CASE WHEN transactioncode IN ('01', '17', '18', '19') THEN amount ELSE 0.00 END) )/100.00 ) totdebitamount
FROM filetransaction
WHERE iscontra=0 AND ISNUMERIC(amount + '.0e0') = 1
GROUP BY fileid
) ft ON ft.fileid=fb.file_id
LEFT JOIN (
SELECT FPSTemp.fileid,
CONVERT(DECIMAL(13,2), SUM(CASE WHEN mt.transaction_code IN ('99', 'Z4', 'Z5') THEN FPSTemp.amount ELSE 0 END )/100.00 ) totcreditamount,
CONVERT(DECIMAL(13,2), SUM(CASE WHEN mt.transaction_code IN ('01', '17', '18', '19') THEN FPSTemp.amount ELSE 0 END )/100.00 ) totdebitamount
FROM filetransaction_excel FPSTemp
INNER JOIN m_transactiontype mt ON mt.name = FPSTemp.transaction_code
WHERE ISNUMERIC(FPSTemp.amount + '.0e0') = 1 AND FPSTemp.amount IS NOT NULL
GROUP BY FPSTemp.fileid
) FTE ON FTE.fileid= fb.file_id
WHERE company_id=@company_id
AND status=0 AND createdby = @INT_USERID
ORDER BY file_id DESC



I have doubts about use of table m_transactiontype in the last derived table.
It seems that "mt.transaction_code" must be replaced with "FPSTemp.transaction_code" and the INNER JOIN removed completely, as columns from the table are not used neither for SELECT not for WHERE clauses.
TheSQLGuru
TheSQLGuru
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12349 Visits: 8547
This is from my Common TSQL Mistakes SQL Saturday session, which I have presented over 50 times now at various venues. Hopefully you can adapt it to your needs (if you haven't already found another solution like simply removing the unwanted data or adding a new column that is just numeric, etc.

KEY TAKEAWAYS

1) Remember that the optimizer can do almost anything it wants with your query as long as algebraically and logically it gives you the same output/effect. I note this may not be the effect you desire! :-)

2) Learn about and use the power of the CASE keyword!

3) Try to not architect multi-variant columns! Wink


Use tempdb
set nocount on
go

IF OBJECT_ID(N'Accounts', N'U') IS NOT NULL
DROP TABLE dbo.Accounts;

CREATE TABLE dbo.Accounts (
account_nbr INT NOT NULL PRIMARY KEY,
account_type VARCHAR(20) NOT NULL
CHECK (account_type IN ('Personal', 'Business Basic', 'Business Plus')),
account_reference VARCHAR(30) NOT NULL);

INSERT dbo.Accounts VALUES(1, 'Personal', 'abc');
INSERT dbo.Accounts VALUES(2, 'Business Basic', '101');
INSERT dbo.Accounts VALUES(3, 'Personal', 'def');
INSERT dbo.Accounts VALUES(4, 'Business Plus', '5');

SELECT account_nbr, account_type, account_reference
FROM dbo.Accounts;


SELECT account_nbr, account_reference AS account_ref_nbr
FROM dbo.Accounts
WHERE account_type LIKE 'Business%'
AND CAST(account_reference AS INT) > 20;

Error:
Conversion failed when converting the varchar value 'abc' to data type int.

SELECT account_nbr, account_ref_nbr
FROM (SELECT account_nbr,
CAST(account_reference AS INT) AS account_ref_nbr
FROM dbo.Accounts
WHERE account_type LIKE 'Business%') AS A
WHERE account_ref_nbr > 20;

Error:
Conversion failed when converting the varchar value 'abc' to data type int.


SELECT account_nbr, account_reference AS account_ref_nbr
FROM dbo.Accounts
WHERE account_type LIKE 'Business%'
AND CASE WHEN account_reference NOT LIKE '%[^0-9]%'
THEN CAST(account_reference AS INT)
END > 20;

SELECT account_nbr, SUM(CAST(account_reference AS INT)) AS account_total
FROM dbo.Accounts
WHERE account_type LIKE 'Business%'
AND CASE WHEN account_reference NOT LIKE '%[^0-9]%'
THEN CAST(account_reference AS INT)
END > 20
GROUP BY account_nbr

DROP TABLE dbo.Accounts;




Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search