using @variable not working on mysql where

Question

Good day sir..

I was trying to find transactions that are not balance..

so I tried running my query

select transCode,sum(debit) as debitx, sum(credit) as creditx, 
sum(credit-debit)as total 
from x_general_transactions_details 
where total != 0
 group by transCode

but above code doesnt work since you cannot use alias on where so I tried using a variable

select transCode,sum(debit) as debitx, sum(credit) as creditx, 
@total := sum(credit-debit) as total 
from x_general_transactions_details 
where @total != 0
group by transCode

this query doesnt give me an error but doesnt give me a result as well, could you please pin point whats wrong with this query?

the goal of this query is just to find which @total is not equal to 0 which means its not balance..

Thank you very much and have a nice day..

O. Jones · Accepted Answer · 2017-03-16 16:10:15Z

1

This is a job for HAVING ...

select transCode,sum(debit) as debitx, sum(credit) as creditx, 
       sum(credit-debit)as total 
  from x_general_transactions_details 
 group by transCode
having sum(credit-debit) != 0

answered Mar 16, 2017 at 16:10

O. Jones

109k17 gold badges134 silver badges187 bronze badges

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

Collectives™ on Stack Overflow

using @variable not working on mysql where

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related