CODEWITHSUNDEEP
mysqlsqldate
Ashishsingh
Ashishsingh

Reputation: 742

sum of debit on the base of month in sql

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

Answers (3)

Taryn
Taryn

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'

See SQL Fiddle with Demo

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

See SQL Fiddle with Demo

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)

See SQL Fiddle with Demo

Upvotes: 3

Olaf Dietsche
Olaf Dietsche

Reputation: 74128

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

podiluska
podiluska

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

Related Questions