How to retrieve total amount of one column that is dependent by another column from mysql -

i retrieve day(dateandtime),sum(amount) group day(dateandtime) when type "withdraw",the amount subtracted instead of adding. how achieve that?

my query now:

  select day(dayandtime),sum(amount) table group day(dayandtime) 

table

id  |   username    |    type       |    amount     |   dateandtime     |               |               |               |  1  |    user1      |    withdraw   |       10      | 2016-01-27 03:35:07   2  |    user2      |    deposit    |       20      | 2016-01-27 03:35:07   3  |    user1      |    deposit    |       40      | 2016-01-27 03:35:07   4  |    user2      |    withdraw   |       20      | 2016-01-27 03:35:07      

you should work case statement this:

select day(dateandtime),sum(case when type='deposit' amount else -amount end)  table1 group day(dateandtime) 

you can test here

---

don't forget made typo in query

dayandtime 

should be

dateandtime