CODEWITHSUNDEEP
mysql
user2806532
user2806532

Reputation: 31

Between clause doesn't work

Trying to set amount range like 0 to 9 10 to 19 ... 50 - 99 but when done individually i.e. a.Amount >50 returns data rows and similarly a.Amount >100 returns data rows but the follwing returns null rows. Please help deadline is near! The Amount is a varchar data type. 

SELECT   DATE_FORMAT((STR_TO_DATE(a.TRANSACTION_DATE,'%d.%m.%Y')), '%Y%m') mnt, 
         COUNT(DISTINCT a.CUSTOMER_ID) totalNum
FROM    credittx a
WHERE   a.COUNTRY = 'Germany'
        AND a.AMOUNT   BETWEEN   100 AND 50
GROUP   BY DATE_FORMAT((STR_TO_DATE(a.TRANSACTION_DATE,'%d.%m.%Y')),'%Y%m')

Upvotes: 0

Views: 48

Answers (3)

R R
R R

Reputation: 2956

As mentioned in the comment that 50 is less than 100 so please change:

AND a.AMOUNT BETWEEN 100 AND 50

to:

AND a.AMOUNT BETWEEN 50 AND 100

Upvotes: 1

Mad Dog Tannen
Mad Dog Tannen

Reputation: 7242

Seems to me that you must have between 50 AND 100. Not 100 AND 50.

SELECT   
DATE_FORMAT((STR_TO_DATE(a.TRANSACTION_DATE,'%d.%m.%Y')), '%Y%m')   mnt, 
COUNT(DISTINCT a.CUSTOMER_ID) totalNum
FROM    credittx a
 WHERE   a.COUNTRY = 'Germany'
 AND         a.AMOUNT   BETWEEN   50 AND 100
 GROUP   BY DATE_FORMAT((STR_TO_DATE(a.TRANSACTION_DATE,'%d.%m.%Y')),'%Y%m')

Upvotes: 1

Sully
Sully

Reputation: 14943

The range is from-to

do BETWEEN 50 AND 100

Upvotes: 2

Related Questions