CODEWITHSUNDEEP
phpmysql
Vinicio Guzman
Vinicio Guzman

Reputation: 133

SELECT COUNT(*) WHERE between 2 numbers in column

I am trying to count how many records I have in my column 'routeorder' that are between 2 numbers. The column has information like:

RouteOrder
ACC-001-01
ACC-001-01
ACC-001-01
ACC-001-01
ACC-015-18
ACC-015-18
ACC-015-18
ACC-015-18
ACC-015-19
ACC-015-19
ACC-015-19
ACC-015-19
ACC-015-19
ACC-016-01
ACC-016-01
ACC-016-01
ACC-017-19
ACC-017-19
ACC-017-19
ACC-017-19
ACC-017-19
ACC-017-19
ACC-017-19

I need to get how many records between 'ACC-001' and 'ACC-016' Is there a way to do it ? This is what I have so far:

SELECT COUNT(*) as areatotals FROM Customers WHERE SUBSTRING(routeorder,LENGTH(routeorder),-3) like 'ACC'

Thanks for the help.

Upvotes: 1

Views: 389

Answers (3)

dognose
dognose

Reputation: 20909

You dont even need to substring or typecast anything. the BETWEEN Operator is also working for strings: SELECT COUNT(*) FROM test WHERE RouteOrder BETWEEN "ACC-002" AND "ACC-007"

http://sqlfiddle.com/#!2/9614c/1

And if you want to include the Upperbound matches also, use SELECT COUNT(*) FROM test WHERE RouteOrder BETWEEN "ACC-002" AND "ACC-007" OR RouteOrder LIKE "ACC-007%"

(the like you need, because when comparing strings ACC-007-1 is technically NOT smaller or equal to ACC-007, which means, the between operator will exclude that)

http://sqlfiddle.com/#!2/9614c/2

Upvotes: 0

echo_Me
echo_Me

Reputation: 37253

try this

SELECT count(*) counts,SUBSTRING(SUBSTRING(RouteOrder , 5),1,3 ) as str 
FROM Customers
GROUP BY str
HAVING str > 001 and str < 016

DEMO HERE

output:

   COUNTS   STR
    9       015

EDIT:

here more simpler and fits your needs if the first part is changing.

 SELECT count(*) counts,SUBSTRING(RouteOrder ,-6 ,3) as str 
 FROM Customers
 GROUP BY str
 HAVING str > 001 and str < 016

DEMO HERE

Upvotes: 2

user1447767
user1447767

Reputation:

Going from the example given on the official documentation, you should be able to that:

SELECT COUNT(*) as areatotals FROM Customers WHERE routeorder BETWEEN 'ACC-001' AND 'ACC-016';

This should trigger a string conversion as described here.

Upvotes: 2

Related Questions