Reputation: 742
I have a table name receipt the scema is as follow
account_no date transaction_type amount
s1 2012-7-7 opening 500
s1 2012-8-13 deposit 1000
s1 2012-7-17 deposit 400
Now I have the following query
select month(r.date),
sum(case when (month(r.date))='7' and r.transaction_type='opening' and r.transaction_type='deposit' then r.amount else '' end )as debit
from receipt r
where r.account_no='s1'
But it is giving me output as:
month debit
7 0
can any one tell me why it is giving me this output ?
Edit
if i want to place the name of month instead of number then how to do that
Upvotes: 0
Views: 123
Reputation: 247880
It seems like you want an OR
condition between r.transaction_type='opening' and r.transaction_type='deposit'
instead of the AND
select date_format(r.date, '%M'),
sum(case when (month(r.date))='7'
and (r.transaction_type='opening'
or r.transaction_type='deposit')
then r.amount else 0 end )as debit
from receipt r
where r.account_no='s1'
Or you can use:
select date_format(r.date, '%M'),
sum(r.amount)
from receipt r
where r.account_no='s1'
and month(r.date) = 7
and r.transaction_type in ('opening', 'deposit')
If you want to get the sum()
for all months, then you will need to add a group by
:
select date_format(r.date, '%M'),
sum(r.amount)
from receipt r
where r.account_no='s1'
and r.transaction_type in ('opening', 'deposit')
group by month(r.date);
OR
select date_format(r.date, '%M'),
sum(case when (r.transaction_type='opening'
or r.transaction_type='deposit')
then r.amount else 0 end )as debit
from receipt r
where r.account_no='s1'
group by month(r.date)
Upvotes: 3
Reputation: 74118
I would rewrite your query to
select month(r.date) as month,
sum(r.amount) as debit
from receipt r
where r.account_no = 's1'
and month(r.date) = 7
and (r.transaction_type = 'opening' or r.transaction_type = 'deposit');
changing the and
to or
as others already pointed out.
Upvotes: 0
Reputation: 51514
You're saying
...r.transaction_type='opening' and r.transaction_type='deposit'
r.transactiontype
is never going to be both 'opening' and 'deposit'
Upvotes: 2