How to use alias variable for condition in mysql?

Question

SELECT mob_no,

  (SELECT max(str_to_date(debit_date,'%d/%m/%Y'))
   FROM client_debit_bal
   WHERE mob_no='".$mobno."') AS date,
       sum(debit_amt) AS debit
FROM client_debit_bal
WHERE date_format(str_to_date(debit_date,'%d/%m/%Y'), '%Y-%m-%d')= date

Actually here date is a alias variable and i cant able to use for condition. Please help me to overcome this problem.

Sample Data's

-----------------------------------
date       | cre_amount  |   mob_no
------------------------------------
23/01/2013 |    100     | 123456
------------------------------------
24/01/2013 |    500     | 123456
------------------------------------
25/01/2013 |    500     | 123456
------------------------------------
26/01/2013 |    900     | 123456
------------------------------------
29/01/2013 |    1000     | 123456
------------------------------------
29/01/2013 |    1300     | 123456
------------------------------------
29/01/2013 |    1200     | 123456
------------------------------------

Expected Output is max(date) and sum(cre_amount) and Result is 3500

(Which means select maximum date and return the sum of cre_amountfrom maximum date)

For future reference, the correct term is "alias". This will be helpful in letter others understand what you're referring to — asprin, Jul 01 '14 at 04:54
Did you look at this? http://stackoverflow.com/questions/200200/can-you-use-an-alias-in-the-where-clause-in-mysql — gpr, Jul 01 '14 at 05:00
***@user3770172***: Show some sample data and some expected rows — Ravinder Reddy, Jul 01 '14 at 05:09

if-else-switch · Accepted Answer · 2014-07-01T05:30:15.000

1

Use simply this

select DATE_FORMAT(max(cast(str_to_date(`date`, '%d/%m/%Y') as date)),'%d/%m/%Y'),
   sum(cre_amount) from table1 where date=(select DATE_FORMAT(max(cast(str_to_date(`date`, '%d/%m/%Y') as date)),'%d/%m/%Y') 
     from table1)

edited Jul 01 '14 at 05:30

answered Jul 01 '14 at 05:23

if-else-switch

977
1
7
24

How to use alias variable for condition in mysql?

1 Answers1