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

1

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

answered Nov 26, 2013 at 11:12

Mad Dog Tannen

7,2505 gold badges34 silver badges55 bronze badges

Comments

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

1

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

edited Nov 26, 2013 at 11:17

answered Nov 26, 2013 at 11:11

R R

2,9562 gold badges27 silver badges43 bronze badges

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