Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Count Rows Between a Range

Posted on 2012-08-11
10
Medium Priority
?
449 Views
Last Modified: 2012-08-13
I have an existing database that was made some time back (16 years) and there wasn't much validation that was taking place.  I have a column called "income" which is Varchar.  Needless to say it had a lot of non numeric characters in it.  I have cleaned those up and am left with results such as 100 or 100.00.  What I need to do now is write a query which would count those records into ranges such as : 0-150, 151-250, 251-500, etc.  I've not been able to locate something that has worked so far.  Any help would be greatly appreciated.
0
Comment
Question by:pposton
  • 3
  • 3
  • 3
  • +1
10 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 38283948
-- Return all rows where income is not numeric
SELECT *
FROM YourTable
WHERE ISNUMERIC(income) = 1

-- Return a count of rows where income is between 0 and 150
SELECT COUNT(*)
FROM YourTable
WHERE (ISNUMERIC(income) = 1 AND income BETWEEN 0 and 150)
0
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 1000 total points
ID: 38283968
Also, the below T-SQL script does a count on multiple ranges in a single statement.
Copy-paste this into your SSMS, verify it works, and modify to meet your needs.


IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
      DROP TABLE #tmp
GO

CREATE TABLE #tmp (income varchar(max))

INSERT INTO #tmp (income)
VALUES ('100'), ('200'), ('300'), ('400'), ('banana'), ('250'), ('0'),('75'),('175'),('foo')

-- This will be the subquery, just to return only numeric values
SELECT income
FROM #tmp
WHERE ISNUMERIC(income) = 1

-- This returns counts for multiple ranges
SELECT
      COUNT(CASE WHEN a.income BETWEEN 0 AND 150 THEN a.income END) as count_0_150,
      COUNT(CASE WHEN a.income BETWEEN 151 AND 300 THEN a.income END) as count_151_300,
      COUNT(CASE WHEN a.income BETWEEN 301 AND 450 THEN a.income END) as count_301_450,
      COUNT(CASE WHEN a.income BETWEEN 451 AND 600 THEN a.income END) as count_451_600
FROM (
      SELECT CAST(income as numeric) as income
      FROM #tmp
      WHERE ISNUMERIC(income) = 1) a
0
 

Author Comment

by:pposton
ID: 38284305
I tried running the code in your first post, but it returns this error: "Conversion failed when converting the varchar value '660.00' to data type int".  Any ideas??
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 35

Expert Comment

by:David Todd
ID: 38285909
Hi,

What version of SQL are you running? The above code worked as is for me. I even added a value of '600.00' to test for possible errors where a decimal point stopped the conversion to integer ...

Regards
  David
0
 

Author Comment

by:pposton
ID: 38286224
I'm running sql 2008 and using ssms.  I get the  error when I run the code:

SELECT COUNT(*)
FROM client
WHERE (ISNUMERIC(income) = 1 AND income BETWEEN 0 and 150)
0
 
LVL 35

Accepted Solution

by:
David Todd earned 1000 total points
ID: 38286468
Hi,

I just spent a few moments trying a few things, and I think for your work you may need to stage the results through a temp table like this:

-- Me being safe
use tempdb
go

-- original query from jimhorn
if OBJECT_ID('tempdb..#tmp') IS NOT NULL 
      DROP TABLE #tmp
GO

CREATE TABLE #tmp (income varchar(max))

INSERT INTO #tmp (income)
VALUES ('100'), ('200'), ('300'), ('400'), ('banana'), ('250'), ('0'),('75'),('175'),('foo')

-- This will be the subquery, just to return only numeric values
SELECT income
FROM #tmp
WHERE ISNUMERIC(income) = 1

-- This returns counts for multiple ranges
SELECT
      COUNT(CASE WHEN a.income BETWEEN 0 AND 150 THEN a.income END) as count_0_150,
      COUNT(CASE WHEN a.income BETWEEN 151 AND 300 THEN a.income END) as count_151_300,
      COUNT(CASE WHEN a.income BETWEEN 301 AND 450 THEN a.income END) as count_301_450,
      COUNT(CASE WHEN a.income BETWEEN 451 AND 600 THEN a.income END) as count_451_600
FROM (
      SELECT CAST(income as numeric) as income
      FROM #tmp
      WHERE ISNUMERIC(income) = 1
	  ) a 

-- /original query from jimhorn
-- Me adding another value

insert #tmp values (' 650.00' )

---- your query which fails
--SELECT COUNT(*)
--FROM #tmp
--WHERE 
--	ISNUMERIC(income) = 1 
--	AND income BETWEEN 0.0 and 150.0 
--;

---- my adaption of your query, which funnily enough also fails.
--select count( * )
--from (
--	select cast( income as numeric ) as income
--	from #tmp
--	where
--		isnumeric( income ) = 1
--	) a
--where
--	a.income BETWEEN 0 and 150
--;

if object_id( 'tmepdb..#stage' ) is not null
	drop table #stage
;

create table #stage(
	income float
	)
;

insert #stage
SELECT CAST(income as float) as income
FROM #tmp
WHERE 
	ISNUMERIC(income) = 1

-- your query which now works
SELECT COUNT(*)
FROM #stage
WHERE 
	ISNUMERIC(income) = 1 
	AND income BETWEEN 0.0 and 150.0 
;

-- my adaption of your query, which now also works.
select count( * )
from (
	select cast( income as numeric ) as income
	from #stage
	where
		isnumeric( income ) = 1
	) a
where
	a.income BETWEEN 0 and 150
;

Open in new window


HTH
  David
0
 
LVL 35

Expert Comment

by:David Todd
ID: 38286475
Hi,

I was testing on SQL 2012.

What I think was happening was SQL is a declaritive language, and the order of operations in the where clause was assuming a procedural environment.

In jims query, the integer processing was happening in the select clause which is processed after the where clause.

I found that even doing things in a sub select didn't work (SQL 2008R2 and SQL 2012), as SQL obviously optimised that out.

Hence the suggestion  if the data is what it is, then you need to use temp tables to get clean data before trying to group it numerically.

HTH
  David
0
 
LVL 25

Expert Comment

by:lwadwell
ID: 38287544
I was thinking that if you cannot change the table definition to a numeric column datatype ... that in the long run a user function may end up being useful.  A rudimentary version is below:
CREATE FUNCTION ReturnNumber(@String varchar(8000))       
returns NUMERIC(18,2)
AS    
BEGIN
    DECLARE @CleanNumber NUMERIC(18,2);
    SET @CleanNumber = NULL;

    IF     ISNUMERIC(@String) = 1 
       AND CHARINDEX('$', @String) = 0
       AND CHARINDEX('e', LOWER(@String)) = 0
        SET @CleanNumber = CAST(@String as NUMERIC(18,2));

    RETURN(@CleanNumber);
END

Open in new window

And use it in the SQL, e.g.
SELECT COUNT(*)
FROM client
WHERE dbo.ReturnNumber(income) BETWEEN 0 and 150

Open in new window

Additional validations beyond ISNUMERIC() where included due to the relaxed nature of the function allowing additional chars.  Test SQL below:
with cte as (
    select '1'          as income union all
    select '22222.22'   as income union all
    select 'foo'        as income union all
    select 'bar'        as income union all
    select '12345'      as income union all
    select '.1'         as income union all
    select '-144.33'    as income union all
    select '+144.33'    as income union all
    select '9-144.33'   as income union all
    select '9+144.33'   as income union all
    select '$333.33'    as income union all
    select '5e2'        as income union all
    select ''           as income union all
    select null         as income
)
SELECT *, ISNUMERIC(income) , dbo.ReturnNumber(income)
FROM cte

Open in new window

0
 

Author Comment

by:pposton
ID: 38287791
Got it...Thanks!  (I found a bad record in the database as well that was throwing an error.  It had '.' with no number associated with it such as '.00'.

One last question.  I'm new to this and have been learning alot but no where near what I need to know.  In the "Count/Case" section it returns the list in columns.  Is there a way to display those in rows instead?  When using the queries with GridViews and Charts it seems that works out better.

Thanks again!!
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 38287817
Thanks for the split.  Good luck with your project.  -Jim

>I'm new to this and have been learning alot
EE is an excellent place for learning..  

> Is there a way to display those in rows instead?
I'd ask it in another question.  Likely it's a PIVOT.
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question