Between clause doesn't work

Question

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')

Sully · Accepted Answer · 2013-11-26 11:11:58Z

2

The range is from-to

do BETWEEN 50 AND 100

answered Nov 26, 2013 at 11:11

Sully

15k5 gold badges56 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mad Dog Tannen · Accepted Answer · 2013-11-26 11:12:06Z

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')

R R · Accepted Answer · 2013-11-26 11:17:06Z

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

Collectives™ on Stack Overflow

Between clause doesn't work

3 Answers 3

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Related