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 4:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, December 18, 2014 1:43 AM
Points: 6,890, Visits: 14,254
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
Post #1449113
Posted Friday, May 3, 2013 4:34 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, December 18, 2014 2:19 AM
Points: 99, Visits: 191
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?
Post #1449118
Posted Friday, May 3, 2013 4:34 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: Today @ 11:53 AM
Points: 3,115, Visits: 3,241
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
www.seavus.com
Post #1449119
Posted Friday, May 3, 2013 5:12 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, December 18, 2014 1:43 AM
Points: 6,890, Visits: 14,254
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
Post #1449132
Posted Friday, May 3, 2013 8:08 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, December 18, 2014 2:19 AM
Points: 99, Visits: 191
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!
Post #1449223
Posted Friday, May 3, 2013 5:04 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:36 PM
Points: 35,776, Visits: 32,448
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1449371
Posted Sunday, May 5, 2013 7:16 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, November 3, 2014 4:30 PM
Points: 4,574, Visits: 8,366
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?

Post #1449557
Posted Sunday, May 5, 2013 7:38 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, November 3, 2014 4:30 PM
Points: 4,574, Visits: 8,366
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?
Post #1449560
Posted Monday, May 6, 2013 12:05 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, November 3, 2014 4:30 PM
Points: 4,574, Visits: 8,366
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.


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.
Post #1449588
Posted Monday, May 6, 2013 6:23 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:36 PM
Points: 4,473, Visits: 6,405
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! ;)

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 at GMail
Post #1449685
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse