How to subtract the sum of some values ​​from others in one table according to different characteristics in a column?

Question

MySQL

Good afternoon. I don’t know how to ask the question correctly, but it’s better to try to tell what is:

Suppose there is a table:

CREATE TABLE IF NOT EXISTS tablename(
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `type` int(11) NOT NULL,
    `sum` varchar(50) NOT NULL default '0',
    PRIMARY KEY ( `id` )	
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

This is an income/expenditure table.
type=0 - income, type=1 - expense
sum - income/expense sum

There are 10 lines, 5 incomes and 5 expenses.
Is it possible to get the result of summing up all incomes (between incomes), expenses (between expenses) and subtract incomes from expenses in one query? That is, to get, as it were, a "balance".

Answer 1

Here is a possible solution:

select
  sum(if(`type` = 0, `sum`, 0)) `income`,
  sum(if(`type` = 1, `sum`, 0)) `outcome`,
  sum(if(`type` = 0, `sum`, -`sum`)) `balance`
from tablename;

Test SQL online

Answer 2

it is possible sum aggregate function can work also on expressions.
https://metanit.com/sql/mysql/6.4.php
classic case is in all dialects of SQL
if - a simplified version in Muska and a number of others
It turns out that we need to calculate for rows that match the conditions the sum of the expression
amount for the operation will be multiplied by 1 or - one.
ps It
is better not to name the field in the database with the names of keywords or functions,
otherwise you will have to quote all the times. I'm talking about the field name sum